/ Check-in [289ee431]
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:Change FTS4 so that if both the content=xxx option and column names are specified, the virtual table assumes that the named columns correspond to columns of table xxx.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | fts4-content
Files: files | file ages | folders
SHA1: 289ee43179369fce2fde50870d72c445e184e896
User & Date: dan 2011-10-05 15:11:30
Context
2011-10-18
12:49
Cherrypick patch [3126754c72] from the trunk into the content= branch. check-in: f9b5b217 user: dan tags: fts4-content
2011-10-05
15:11
Change FTS4 so that if both the content=xxx option and column names are specified, the virtual table assumes that the named columns correspond to columns of table xxx. check-in: 289ee431 user: dan tags: fts4-content
06:07
Fix a problem with IO error handling in the rebuild-index code. check-in: c6ba81fc user: dan tags: fts4-content
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/fts3/fts3.c.

1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196


1197
1198

1199
1200
1201
1202
1203
1204
1205
....
1452
1453
1454
1455
1456
1457
1458

























1459
1460
1461
1462
1463
1464
1465

1466




1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482

1483
1484
1485



1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
....
2844
2845
2846
2847
2848
2849
2850
2851
2852

2853
2854
2855
2856
2857
2858
2859
2860
2861
2862


2863
2864


2865
2866
2867
2868


2869
2870
2871
2872
2873
2874
2875
    }
  }

  /* If a content=xxx option was specified, the following:
  **
  **   1. Ignore any compress= and uncompress= options.
  **
  **   2. Ignore any column names that were specified as part of the 
  **      the CREATE VIRTUAL TABLE statement.
  **
  **   3. Determine the actual column names to use for the FTS table 
  **      based on the columns of the content= table.
  */
  if( rc==SQLITE_OK && zContent ){
    sqlite3_free(aCol); 
    sqlite3_free(zCompress); 
    sqlite3_free(zUncompress); 
    zCompress = 0;
    zUncompress = 0;


    aCol = 0;
    rc = fts3ContentColumns(db, argv[1], zContent, &aCol, &nCol, &nString);

    assert( rc!=SQLITE_OK || nCol>0 );
  }
  if( rc!=SQLITE_OK ) goto fts3_init_out;

  if( nCol==0 ){
    assert( nString==0 );
    aCol[0] = "content";
................................................................................
  sqlite3Fts3FreeDeferredTokens(pCsr);
  sqlite3_free(pCsr->aDoclist);
  sqlite3_free(pCsr->aMatchinfo);
  assert( ((Fts3Table *)pCsr->base.pVtab)->pSegments==0 );
  sqlite3_free(pCsr);
  return SQLITE_OK;
}


























/*
** Position the pCsr->pStmt statement so that it is on the row
** of the %_content table that contains the last match.  Return
** SQLITE_OK on success.  
*/
static int fts3CursorSeek(sqlite3_context *pContext, Fts3Cursor *pCsr){

  if( pCsr->isRequireSeek ){




    sqlite3_bind_int64(pCsr->pStmt, 1, pCsr->iPrevId);
    pCsr->isRequireSeek = 0;
    if( SQLITE_ROW==sqlite3_step(pCsr->pStmt) ){
      return SQLITE_OK;
    }else{
      int rc = sqlite3_reset(pCsr->pStmt);
      if( rc==SQLITE_OK ){
        Fts3Table *p = (Fts3Table *)pCsr->base.pVtab;
        if( p->zContentTbl==0 ){
          /* If no row was found and no error has occured, then the %_content
          ** table is missing a row that is present in the full-text index.
          ** The data structures are corrupt.
          */
          rc = SQLITE_CORRUPT_VTAB;
        }else{
          return SQLITE_OK;

        }
      }
      pCsr->isEof = 1;



      if( pContext ){
        sqlite3_result_error_code(pContext, rc);
      }
      return rc;
    }
  }else{
    return SQLITE_OK;
  }
}

/*
** This function is used to process a single interior node when searching
** a b-tree for a term or term prefix. The node data is passed to this 
** function via the zNode/nNode parameters. The term to search for is
** passed in zTerm/nTerm.
................................................................................

  /* Compile a SELECT statement for this cursor. For a full-table-scan, the
  ** statement loops through all rows of the %_content table. For a
  ** full-text query or docid lookup, the statement retrieves a single
  ** row by docid.
  */
  if( idxNum==FTS3_FULLSCAN_SEARCH ){
    const char *zTmpl = "SELECT %s ORDER BY rowid %s";
    zSql = sqlite3_mprintf(zTmpl, 

        p->zReadExprlist, (pCsr->bDesc ? "DESC" : "ASC")
    );
  }else{
    const char *zTmpl = "SELECT %s WHERE rowid = ?";
    zSql = sqlite3_mprintf(zTmpl, p->zReadExprlist);
  }
  if( !zSql ) return SQLITE_NOMEM;
  rc = sqlite3_prepare_v2(p->db, zSql, -1, &pCsr->pStmt, 0);
  sqlite3_free(zSql);
  if( rc!=SQLITE_OK ) return rc;



  if( idxNum==FTS3_DOCID_SEARCH ){


    rc = sqlite3_bind_value(pCsr->pStmt, 1, apVal[0]);
    if( rc!=SQLITE_OK ) return rc;
  }



  return fts3NextMethod(pCursor);
}

/* 
** This is the xEof method of the virtual table. SQLite calls this 
** routine to find out if it has reached the end of a result set.
*/







|
|
<
<
<


<




>
>
|
|
>







 







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







>

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


|
<

<
<
>


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







 







<
|
>


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







1178
1179
1180
1181
1182
1183
1184
1185
1186



1187
1188

1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
....
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486
1487
1488
1489
1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501

1502

1503
1504
1505

1506


1507
1508
1509

1510
1511
1512
1513
1514
1515
1516




1517
1518
1519
1520
1521
1522
1523
....
2867
2868
2869
2870
2871
2872
2873

2874
2875
2876
2877
2878




2879
2880

2881
2882
2883
2884
2885
2886
2887

2888
2889
2890
2891
2892
2893
2894
2895
2896
2897
2898
    }
  }

  /* If a content=xxx option was specified, the following:
  **
  **   1. Ignore any compress= and uncompress= options.
  **
  **   2. If no column names were specified as part of the CREATE VIRTUAL
  **      TABLE statement, use all columns from the content table.



  */
  if( rc==SQLITE_OK && zContent ){

    sqlite3_free(zCompress); 
    sqlite3_free(zUncompress); 
    zCompress = 0;
    zUncompress = 0;
    if( nCol==0 ){
      sqlite3_free(aCol); 
      aCol = 0;
      rc = fts3ContentColumns(db, argv[1], zContent, &aCol, &nCol, &nString);
    }
    assert( rc!=SQLITE_OK || nCol>0 );
  }
  if( rc!=SQLITE_OK ) goto fts3_init_out;

  if( nCol==0 ){
    assert( nString==0 );
    aCol[0] = "content";
................................................................................
  sqlite3Fts3FreeDeferredTokens(pCsr);
  sqlite3_free(pCsr->aDoclist);
  sqlite3_free(pCsr->aMatchinfo);
  assert( ((Fts3Table *)pCsr->base.pVtab)->pSegments==0 );
  sqlite3_free(pCsr);
  return SQLITE_OK;
}

/*
** If pCsr->pStmt has not been prepared (i.e. if pCsr->pStmt==0), then
** compose and prepare an SQL statement of the form:
**
**    "SELECT <columns> FROM %_content WHERE rowid = ?"
**
** (or the equivalent for a content=xxx table) and set pCsr->pStmt to
** it. If an error occurs, return an SQLite error code.
**
** Otherwise, set *ppStmt to point to pCsr->pStmt and return SQLITE_OK.
*/
static int fts3CursorSeekStmt(Fts3Cursor *pCsr, sqlite3_stmt **ppStmt){
  int rc = SQLITE_OK;
  if( pCsr->pStmt==0 ){
    Fts3Table *p = (Fts3Table *)pCsr->base.pVtab;
    char *zSql;
    zSql = sqlite3_mprintf("SELECT %s WHERE rowid = ?", p->zReadExprlist);
    if( !zSql ) return SQLITE_NOMEM;
    rc = sqlite3_prepare_v2(p->db, zSql, -1, &pCsr->pStmt, 0);
    sqlite3_free(zSql);
  }
  *ppStmt = pCsr->pStmt;
  return rc;
}

/*
** Position the pCsr->pStmt statement so that it is on the row
** of the %_content table that contains the last match.  Return
** SQLITE_OK on success.  
*/
static int fts3CursorSeek(sqlite3_context *pContext, Fts3Cursor *pCsr){
  int rc = SQLITE_OK;
  if( pCsr->isRequireSeek ){
    sqlite3_stmt *pStmt = 0;

    rc = fts3CursorSeekStmt(pCsr, &pStmt);
    if( rc==SQLITE_OK ){
      sqlite3_bind_int64(pCsr->pStmt, 1, pCsr->iPrevId);
      pCsr->isRequireSeek = 0;
      if( SQLITE_ROW==sqlite3_step(pCsr->pStmt) ){
        return SQLITE_OK;
      }else{
        rc = sqlite3_reset(pCsr->pStmt);

        if( rc==SQLITE_OK && ((Fts3Table *)pCsr->base.pVtab)->zContentTbl==0 ){

          /* If no row was found and no error has occured, then the %_content
          ** table is missing a row that is present in the full-text index.
          ** The data structures are corrupt.  */

          rc = SQLITE_CORRUPT_VTAB;


          pCsr->isEof = 1;
        }
      }

    }
  }

  if( rc!=SQLITE_OK && pContext ){
    sqlite3_result_error_code(pContext, rc);
  }
  return rc;




}

/*
** This function is used to process a single interior node when searching
** a b-tree for a term or term prefix. The node data is passed to this 
** function via the zNode/nNode parameters. The term to search for is
** passed in zTerm/nTerm.
................................................................................

  /* Compile a SELECT statement for this cursor. For a full-table-scan, the
  ** statement loops through all rows of the %_content table. For a
  ** full-text query or docid lookup, the statement retrieves a single
  ** row by docid.
  */
  if( idxNum==FTS3_FULLSCAN_SEARCH ){

    zSql = sqlite3_mprintf(
        "SELECT %s ORDER BY rowid %s",
        p->zReadExprlist, (pCsr->bDesc ? "DESC" : "ASC")
    );
    if( zSql ){




      rc = sqlite3_prepare_v2(p->db, zSql, -1, &pCsr->pStmt, 0);
      sqlite3_free(zSql);

    }else{
      rc = SQLITE_NOMEM;
    }
  }else if( idxNum==FTS3_DOCID_SEARCH ){
    rc = fts3CursorSeekStmt(pCsr, &pCsr->pStmt);
    if( rc==SQLITE_OK ){
      rc = sqlite3_bind_value(pCsr->pStmt, 1, apVal[0]);

    }
  }
  if( rc!=SQLITE_OK ) return rc;

  return fts3NextMethod(pCursor);
}

/* 
** This is the xEof method of the virtual table. SQLite calls this 
** routine to find out if it has reached the end of a result set.
*/

Changes to ext/fts3/fts3_write.c.

405
406
407
408
409
410
411


412
413
414
415
416

417
418
419
420
421
422




423
424
425
426
427
428
429
** write-locks on the %_segments and %_segdir ** tables). 
**
** We try to avoid this because if FTS3 returns any error when committing
** a transaction, the whole transaction will be rolled back. And this is
** not what users expect when they get SQLITE_LOCKED_SHAREDCACHE. It can
** still happen if the user reads data directly from the %_segments or
** %_segdir tables instead of going through FTS3 though.


*/
int sqlite3Fts3ReadLock(Fts3Table *p){
  int rc;                         /* Return code */
  sqlite3_stmt *pStmt;            /* Statement used to obtain lock */


  rc = fts3SqlStmt(p, SQL_SELECT_CONTENT_BY_ROWID, &pStmt, 0);
  if( rc==SQLITE_OK ){
    sqlite3_bind_null(pStmt, 1);
    sqlite3_step(pStmt);
    rc = sqlite3_reset(pStmt);
  }




  return rc;
}

/*
** Set *ppStmt to a statement handle that may be used to iterate through
** all rows in the %_segdir table, from oldest to newest. If successful,
** return SQLITE_OK. If an error occurs while preparing the statement, 







>
>





>
|
|
|
|
|
|
>
>
>
>







405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
** write-locks on the %_segments and %_segdir ** tables). 
**
** We try to avoid this because if FTS3 returns any error when committing
** a transaction, the whole transaction will be rolled back. And this is
** not what users expect when they get SQLITE_LOCKED_SHAREDCACHE. It can
** still happen if the user reads data directly from the %_segments or
** %_segdir tables instead of going through FTS3 though.
**
** This reasoning does not apply to a content=xxx table.
*/
int sqlite3Fts3ReadLock(Fts3Table *p){
  int rc;                         /* Return code */
  sqlite3_stmt *pStmt;            /* Statement used to obtain lock */

  if( p->zContentTbl==0 ){
    rc = fts3SqlStmt(p, SQL_SELECT_CONTENT_BY_ROWID, &pStmt, 0);
    if( rc==SQLITE_OK ){
      sqlite3_bind_null(pStmt, 1);
      sqlite3_step(pStmt);
      rc = sqlite3_reset(pStmt);
    }
  }else{
    rc = SQLITE_OK;
  }

  return rc;
}

/*
** Set *ppStmt to a statement handle that may be used to iterate through
** all rows in the %_segdir table, from oldest to newest. If successful,
** return SQLITE_OK. If an error occurs while preparing the statement, 

Changes to test/fts4content.test.

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
...
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
















451
#   5.* - Check that CREATE TABLE, DROP TABLE and ALTER TABLE correctly
#         ignore any %_content table when used with the content=xxx option.
#
#   6.* - Test the effects of messing with the schema of table xxx after
#         creating a content=xxx FTS index.
#

do_execsql_test 1.1 {
  CREATE TABLE t1(a, b, c);
  INSERT INTO t1 VALUES('w x', 'x y', 'y z');
  CREATE VIRTUAL TABLE ft1 USING fts4(content=t1);
}

do_execsql_test 1.2 {
  PRAGMA table_info(ft1);
} {
  0 a {} 0 {} 0 
  1 b {} 0 {} 0 
  2 c {} 0 {} 0
}

do_execsql_test 1.3 { SELECT *, rowid FROM ft1 } {{w x} {x y} {y z} 1}
do_execsql_test 1.4 { SELECT a, c FROM ft1 WHERE rowid=1 } {{w x} {y z}}

do_execsql_test 1.5 { INSERT INTO ft1(ft1) VALUES('rebuild') } {}
do_execsql_test 1.6 { SELECT rowid FROM ft1 WHERE ft1 MATCH 'x' } {1}
do_execsql_test 1.7 { SELECT rowid FROM ft1 WHERE ft1 MATCH 'a' } {}












#-------------------------------------------------------------------------
# The following block of tests - 2.* - test that a content=xxx FTS table
# can be queried. Also tested are cases where rows identified in the FTS
# are missing from the content table, and cases where the index is 
# inconsistent with the content table.
# 
................................................................................
} {2}

do_execsql_test 6.2.7 {
  DROP TABLE t7;
  CREATE TABLE t7(x);
}
do_catchsql_test 6.2.8 {
  SELECT rowid FROM ft7 WHERE ft7 MATCH '"A A"';
} {1 {SQL logic error or missing database}}
do_catchsql_test 6.2.9 {
  SELECT rowid FROM ft7 WHERE ft7 MATCH '"A A"';
} {1 {SQL logic error or missing database}}

db close
sqlite3 db test.db
do_catchsql_test 6.2.10 {
  SELECT rowid FROM ft7 WHERE ft7 MATCH '"A A"';
} {0 2}
do_catchsql_test 6.2.11 {
  SELECT rowid, * FROM ft7 WHERE ft7 MATCH '"A A"';
} {0 {2 {}}}

















finish_test







|





|







|
|

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







 







|


|











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

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
...
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
#   5.* - Check that CREATE TABLE, DROP TABLE and ALTER TABLE correctly
#         ignore any %_content table when used with the content=xxx option.
#
#   6.* - Test the effects of messing with the schema of table xxx after
#         creating a content=xxx FTS index.
#

do_execsql_test 1.1.1 {
  CREATE TABLE t1(a, b, c);
  INSERT INTO t1 VALUES('w x', 'x y', 'y z');
  CREATE VIRTUAL TABLE ft1 USING fts4(content=t1);
}

do_execsql_test 1.1.2 {
  PRAGMA table_info(ft1);
} {
  0 a {} 0 {} 0 
  1 b {} 0 {} 0 
  2 c {} 0 {} 0
}

do_execsql_test 1.1.3 { SELECT *, rowid FROM ft1 } {{w x} {x y} {y z} 1}
do_execsql_test 1.1.4 { SELECT a, c FROM ft1 WHERE rowid=1 } {{w x} {y z}}

do_execsql_test 1.1.5 { INSERT INTO ft1(ft1) VALUES('rebuild') } {}
do_execsql_test 1.1.6 { SELECT rowid FROM ft1 WHERE ft1 MATCH 'x' } {1}
do_execsql_test 1.1.7 { SELECT rowid FROM ft1 WHERE ft1 MATCH 'a' } {}

do_execsql_test 1.2.1 {
  DROP TABLE ft1;
  CREATE VIRTUAL TABLE ft1 USING fts4(content=t1, b);
  PRAGMA table_info(ft1);
} {
  0 b {} 0 {} 0 
}
do_execsql_test 1.2.2 { 
  SELECT *, rowid FROM ft1 
} {{x y} 1}

#-------------------------------------------------------------------------
# The following block of tests - 2.* - test that a content=xxx FTS table
# can be queried. Also tested are cases where rows identified in the FTS
# are missing from the content table, and cases where the index is 
# inconsistent with the content table.
# 
................................................................................
} {2}

do_execsql_test 6.2.7 {
  DROP TABLE t7;
  CREATE TABLE t7(x);
}
do_catchsql_test 6.2.8 {
  SELECT * FROM ft7 WHERE ft7 MATCH '"A A"';
} {1 {SQL logic error or missing database}}
do_catchsql_test 6.2.9 {
  SELECT * FROM ft7 WHERE ft7 MATCH '"A A"';
} {1 {SQL logic error or missing database}}

db close
sqlite3 db test.db
do_catchsql_test 6.2.10 {
  SELECT rowid FROM ft7 WHERE ft7 MATCH '"A A"';
} {0 2}
do_catchsql_test 6.2.11 {
  SELECT rowid, * FROM ft7 WHERE ft7 MATCH '"A A"';
} {0 {2 {}}}

#-------------------------------------------------------------------------
# Test cases 7.*
# 
do_execsql_test 7.1.1 {
  CREATE VIRTUAL TABLE ft8 USING fts4(content=nosuchtable, x);
  INSERT INTO ft8(docid, x) VALUES(13, 'U O N X G');
  INSERT INTO ft8(docid, x) VALUES(14, 'C J J U B');
  INSERT INTO ft8(docid, x) VALUES(15, 'N J Y G X');
  INSERT INTO ft8(docid, x) VALUES(16, 'R Y D O R');
  INSERT INTO ft8(docid, x) VALUES(17, 'I Y T Q O');
}

do_execsql_test 7.1.2 {
  SELECT docid FROM ft8 WHERE ft8 MATCH 'N';
} {13 15}

finish_test