SQLite

Check-in [e38e2bb637]
Login

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

Overview
Comment:Change the fts5 content= option so that it matches fts5 columns with the underlying table columns by name, not by their position within the CREATE TABLE statement.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | fts5
Files: files | file ages | folders
SHA1: e38e2bb637844dae8ae5d5f3e23d8369e1b91e45
User & Date: dan 2015-04-27 16:21:49.481
Context
2015-04-28
18:35
Improve coverage of fts5 tests. (check-in: 8e8136f2dc user: dan tags: fts5)
2015-04-27
16:21
Change the fts5 content= option so that it matches fts5 columns with the underlying table columns by name, not by their position within the CREATE TABLE statement. (check-in: e38e2bb637 user: dan tags: fts5)
11:31
Further tests for fts5. (check-in: ffeb3ef3cf user: dan tags: fts5)
Changes
Unified Diff Ignore Whitespace Patch
Changes to ext/fts5/fts5Int.h.
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
**
** And all information loaded from the %_config table.
**
** nAutomerge:
**   The minimum number of segments that an auto-merge operation should
**   attempt to merge together. A value of 1 sets the object to use the 
**   compile time default. Zero disables auto-merge altogether.










*/
struct Fts5Config {
  sqlite3 *db;                    /* Database handle */
  char *zDb;                      /* Database holding FTS index (e.g. "main") */
  char *zName;                    /* Name of FTS index */
  int nCol;                       /* Number of columns */
  char **azCol;                   /* Column names */
  u8 *abUnindexed;                /* True for unindexed columns */
  int nPrefix;                    /* Number of prefix indexes */
  int *aPrefix;                   /* Sizes in bytes of nPrefix prefix indexes */
  int eContent;                   /* An FTS5_CONTENT value */
  char *zContent;                 /* content table */ 
  char *zContentRowid;            /* "content_rowid=" option value */ 

  Fts5Tokenizer *pTok;
  fts5_tokenizer *pTokApi;

  /* Values loaded from the %_config table */
  int iCookie;                    /* Incremented when %_config is modified */
  int pgsz;                       /* Approximate page size used in %_data */
  int nAutomerge;                 /* 'automerge' setting */







>
>
>
>
>
>
>
>
>
>













>







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
**
** And all information loaded from the %_config table.
**
** nAutomerge:
**   The minimum number of segments that an auto-merge operation should
**   attempt to merge together. A value of 1 sets the object to use the 
**   compile time default. Zero disables auto-merge altogether.
**
** zContent:
**
** zContentRowid:
**   The value of the content_rowid= option, if one was specified. Or 
**   the string "rowid" otherwise. This text is not quoted - if it is
**   used as part of an SQL statement it needs to be quoted appropriately.
**
** zContentExprlist:
**
*/
struct Fts5Config {
  sqlite3 *db;                    /* Database handle */
  char *zDb;                      /* Database holding FTS index (e.g. "main") */
  char *zName;                    /* Name of FTS index */
  int nCol;                       /* Number of columns */
  char **azCol;                   /* Column names */
  u8 *abUnindexed;                /* True for unindexed columns */
  int nPrefix;                    /* Number of prefix indexes */
  int *aPrefix;                   /* Sizes in bytes of nPrefix prefix indexes */
  int eContent;                   /* An FTS5_CONTENT value */
  char *zContent;                 /* content table */ 
  char *zContentRowid;            /* "content_rowid=" option value */ 
  char *zContentExprlist;
  Fts5Tokenizer *pTok;
  fts5_tokenizer *pTokApi;

  /* Values loaded from the %_config table */
  int iCookie;                    /* Incremented when %_config is modified */
  int pgsz;                       /* Approximate page size used in %_data */
  int nAutomerge;                 /* 'automerge' setting */
Changes to ext/fts5/fts5_config.c.
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380

  if( sqlite3_strnicmp("content_rowid", zCmd, nCmd)==0 ){
    int rc = SQLITE_OK;
    if( pConfig->zContentRowid ){
      *pzErr = sqlite3_mprintf("multiple content_rowid=... directives");
      rc = SQLITE_ERROR;
    }else{
      pConfig->zContentRowid = fts5EscapeName(&rc, zArg);
    }
    return rc;
  }

  *pzErr = sqlite3_mprintf("unrecognized option: \"%.*s\"", nCmd, zCmd);
  return SQLITE_ERROR;
}







|







366
367
368
369
370
371
372
373
374
375
376
377
378
379
380

  if( sqlite3_strnicmp("content_rowid", zCmd, nCmd)==0 ){
    int rc = SQLITE_OK;
    if( pConfig->zContentRowid ){
      *pzErr = sqlite3_mprintf("multiple content_rowid=... directives");
      rc = SQLITE_ERROR;
    }else{
      pConfig->zContentRowid = fts5Strdup(&rc, zArg);
    }
    return rc;
  }

  *pzErr = sqlite3_mprintf("unrecognized option: \"%.*s\"", nCmd, zCmd);
  return SQLITE_ERROR;
}
464
465
466
467
468
469
470

























471
472
473
474
475
476
477
      rc = SQLITE_ERROR;
    }
  }

  p->azCol[p->nCol++] = zCol;
  return rc;
}


























/*
** Arguments nArg/azArg contain the string arguments passed to the xCreate
** or xConnect method of the virtual table. This function attempts to 
** allocate an instance of Fts5Config containing the results of parsing
** those arguments.
**







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







464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
      rc = SQLITE_ERROR;
    }
  }

  p->azCol[p->nCol++] = zCol;
  return rc;
}

/*
** Populate the Fts5Config.zContentExprlist string.
*/
static int fts5ConfigMakeExprlist(Fts5Config *p){
  int i;
  int rc = SQLITE_OK;
  Fts5Buffer buf = {0, 0, 0};
  const char *zSep = "";

  sqlite3Fts5BufferAppendPrintf(&rc, &buf, "T.%Q", p->zContentRowid);
  if( p->eContent!=FTS5_CONTENT_NONE ){
    for(i=0; i<p->nCol; i++){
      if( p->eContent==FTS5_CONTENT_EXTERNAL ){
        sqlite3Fts5BufferAppendPrintf(&rc, &buf, ", T.%Q", p->azCol[i]);
      }else{
        sqlite3Fts5BufferAppendPrintf(&rc, &buf, ", T.c%d", i);
      }
    }
  }

  assert( p->zContentExprlist==0 );
  p->zContentExprlist = (char*)buf.p;
  return rc;
}

/*
** Arguments nArg/azArg contain the string arguments passed to the xCreate
** or xConnect method of the virtual table. This function attempts to 
** allocate an instance of Fts5Config containing the results of parsing
** those arguments.
**
566
567
568
569
570
571
572





573
574
575
576
577
578
579
      sqlite3_free(pRet->zContentRowid);
      pRet->zContentRowid = 0;
    }
  }
  if( rc==SQLITE_OK && pRet->zContentRowid==0 ){
    pRet->zContentRowid = fts5Strdup(&rc, "rowid");
  }






  if( rc!=SQLITE_OK ){
    sqlite3Fts5ConfigFree(pRet);
    *ppOut = 0;
  }
  return rc;
}







>
>
>
>
>







591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
      sqlite3_free(pRet->zContentRowid);
      pRet->zContentRowid = 0;
    }
  }
  if( rc==SQLITE_OK && pRet->zContentRowid==0 ){
    pRet->zContentRowid = fts5Strdup(&rc, "rowid");
  }

  /* Formulate the zContentExprlist text */
  if( rc==SQLITE_OK ){
    rc = fts5ConfigMakeExprlist(pRet);
  }

  if( rc!=SQLITE_OK ){
    sqlite3Fts5ConfigFree(pRet);
    *ppOut = 0;
  }
  return rc;
}
594
595
596
597
598
599
600

601
602
603
604
605
606
607
    }
    sqlite3_free(pConfig->azCol);
    sqlite3_free(pConfig->aPrefix);
    sqlite3_free(pConfig->zRank);
    sqlite3_free(pConfig->zRankArgs);
    sqlite3_free(pConfig->zContent);
    sqlite3_free(pConfig->zContentRowid);

    sqlite3_free(pConfig);
  }
}

/*
** Call sqlite3_declare_vtab() based on the contents of the configuration
** object passed as the only argument. Return SQLITE_OK if successful, or







>







624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
    }
    sqlite3_free(pConfig->azCol);
    sqlite3_free(pConfig->aPrefix);
    sqlite3_free(pConfig->zRank);
    sqlite3_free(pConfig->zRankArgs);
    sqlite3_free(pConfig->zContent);
    sqlite3_free(pConfig->zContentRowid);
    sqlite3_free(pConfig->zContentExprlist);
    sqlite3_free(pConfig);
  }
}

/*
** Call sqlite3_declare_vtab() based on the contents of the configuration
** object passed as the only argument. Return SQLITE_OK if successful, or
Changes to ext/fts5/fts5_index.c.
4725
4726
4727
4728
4729
4730
4731
4732
4733
4734
4735
4736
4737
4738
4739
static void fts5IndexIntegrityCheckSegment(
  Fts5Index *p,                   /* FTS5 backend object */
  int iIdx,                       /* Index that pSeg is a part of */
  Fts5StructureSegment *pSeg      /* Segment to check internal consistency */
){
  Fts5BtreeIter iter;             /* Used to iterate through b-tree hierarchy */

  if( pSeg->pgnoFirst==0 && pSeg->pgnoLast==0 ) return;

  /* Iterate through the b-tree hierarchy.  */
  for(fts5BtreeIterInit(p, iIdx, pSeg, &iter);
      p->rc==SQLITE_OK && iter.bEof==0;
      fts5BtreeIterNext(&iter)
  ){
    i64 iRow;                     /* Rowid for this leaf */







|







4725
4726
4727
4728
4729
4730
4731
4732
4733
4734
4735
4736
4737
4738
4739
static void fts5IndexIntegrityCheckSegment(
  Fts5Index *p,                   /* FTS5 backend object */
  int iIdx,                       /* Index that pSeg is a part of */
  Fts5StructureSegment *pSeg      /* Segment to check internal consistency */
){
  Fts5BtreeIter iter;             /* Used to iterate through b-tree hierarchy */

  if( pSeg->pgnoFirst==0 ) return;

  /* Iterate through the b-tree hierarchy.  */
  for(fts5BtreeIterInit(p, iIdx, pSeg, &iter);
      p->rc==SQLITE_OK && iter.bEof==0;
      fts5BtreeIterNext(&iter)
  ){
    i64 iRow;                     /* Rowid for this leaf */
5144
5145
5146
5147
5148
5149
5150
5151
5152
5153
5154
5155
5156
5157
5158
5159
5160
5161
**
** The return value is the number of bytes read from the input buffer.
*/
static int fts5DecodeDoclist(int *pRc, Fts5Buffer *pBuf, const u8 *a, int n){
  i64 iDocid;
  int iOff = 0;

  if( iOff<n ){
    iOff += sqlite3GetVarint(&a[iOff], (u64*)&iDocid);
    sqlite3Fts5BufferAppendPrintf(pRc, pBuf, " rowid=%lld", iDocid);
  }
  while( iOff<n ){
    int nPos;
    int bDummy;
    iOff += fts5GetPoslistSize(&a[iOff], &nPos, &bDummy);
    iOff += fts5DecodePoslist(pRc, pBuf, &a[iOff], MIN(n-iOff, nPos));
    if( iOff<n ){
      i64 iDelta;







<
|
|
<







5144
5145
5146
5147
5148
5149
5150

5151
5152

5153
5154
5155
5156
5157
5158
5159
**
** The return value is the number of bytes read from the input buffer.
*/
static int fts5DecodeDoclist(int *pRc, Fts5Buffer *pBuf, const u8 *a, int n){
  i64 iDocid;
  int iOff = 0;


  iOff = sqlite3GetVarint(&a[iOff], (u64*)&iDocid);
  sqlite3Fts5BufferAppendPrintf(pRc, pBuf, " rowid=%lld", iDocid);

  while( iOff<n ){
    int nPos;
    int bDummy;
    iOff += fts5GetPoslistSize(&a[iOff], &nPos, &bDummy);
    iOff += fts5DecodePoslist(pRc, pBuf, &a[iOff], MIN(n-iOff, nPos));
    if( iOff<n ){
      i64 iDelta;
Changes to ext/fts5/fts5_storage.c.
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
  char **pzErrMsg                 /* OUT: Error message (if any) */
){
  int rc = SQLITE_OK;

  assert( eStmt>=0 && eStmt<ArraySize(p->aStmt) );
  if( p->aStmt[eStmt]==0 ){
    const char *azStmt[] = {
      "SELECT * FROM %s ORDER BY %s ASC",               /* SCAN_ASC */
      "SELECT * FROM %s ORDER BY %s DESC",              /* SCAN_DESC */
      "SELECT * FROM %s WHERE %s=?",                    /* LOOKUP  */

      "INSERT INTO %Q.'%q_content' VALUES(%s)",         /* INSERT_CONTENT  */
      "REPLACE INTO %Q.'%q_content' VALUES(%s)",        /* REPLACE_CONTENT */
      "DELETE FROM %Q.'%q_content' WHERE id=?",         /* DELETE_CONTENT  */
      "REPLACE INTO %Q.'%q_docsize' VALUES(?,?)",       /* REPLACE_DOCSIZE  */
      "DELETE FROM %Q.'%q_docsize' WHERE id=?",         /* DELETE_DOCSIZE  */

      "SELECT sz FROM %Q.'%q_docsize' WHERE id=?",      /* LOOKUP_DOCSIZE  */

      "REPLACE INTO %Q.'%q_config' VALUES(?,?)",        /* REPLACE_CONFIG */
    };
    Fts5Config *pC = p->pConfig;
    char *zSql = 0;

    switch( eStmt ){
      case FTS5_STMT_SCAN_ASC:
      case FTS5_STMT_SCAN_DESC:
      case FTS5_STMT_LOOKUP:
        zSql = sqlite3_mprintf(azStmt[eStmt], pC->zContent, pC->zContentRowid);


        break;

      case FTS5_STMT_INSERT_CONTENT: 
      case FTS5_STMT_REPLACE_CONTENT: {
        int nCol = pC->nCol + 1;
        char *zBind;
        int i;







|
|
|


















|
>
>







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
  char **pzErrMsg                 /* OUT: Error message (if any) */
){
  int rc = SQLITE_OK;

  assert( eStmt>=0 && eStmt<ArraySize(p->aStmt) );
  if( p->aStmt[eStmt]==0 ){
    const char *azStmt[] = {
      "SELECT %s FROM %s T ORDER BY T.%Q ASC",          /* SCAN_ASC */
      "SELECT %s FROM %s T ORDER BY T.%Q DESC",         /* SCAN_DESC */
      "SELECT %s FROM %s T WHERE    T.%Q=?",            /* LOOKUP  */

      "INSERT INTO %Q.'%q_content' VALUES(%s)",         /* INSERT_CONTENT  */
      "REPLACE INTO %Q.'%q_content' VALUES(%s)",        /* REPLACE_CONTENT */
      "DELETE FROM %Q.'%q_content' WHERE id=?",         /* DELETE_CONTENT  */
      "REPLACE INTO %Q.'%q_docsize' VALUES(?,?)",       /* REPLACE_DOCSIZE  */
      "DELETE FROM %Q.'%q_docsize' WHERE id=?",         /* DELETE_DOCSIZE  */

      "SELECT sz FROM %Q.'%q_docsize' WHERE id=?",      /* LOOKUP_DOCSIZE  */

      "REPLACE INTO %Q.'%q_config' VALUES(?,?)",        /* REPLACE_CONFIG */
    };
    Fts5Config *pC = p->pConfig;
    char *zSql = 0;

    switch( eStmt ){
      case FTS5_STMT_SCAN_ASC:
      case FTS5_STMT_SCAN_DESC:
      case FTS5_STMT_LOOKUP:
        zSql = sqlite3_mprintf(azStmt[eStmt], 
            pC->zContentExprlist, pC->zContent, pC->zContentRowid
        );
        break;

      case FTS5_STMT_INSERT_CONTENT: 
      case FTS5_STMT_REPLACE_CONTENT: {
        int nCol = pC->nCol + 1;
        char *zBind;
        int i;
Changes to ext/fts5/test/fts5content.test.
1
2
3
4
5
6
7
8
9
10
11

12
13
14
15
16
17
18
# 2014 Dec 20
#
# 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.
#
#***********************************************************************
#

#

source [file join [file dirname [info script]] fts5_common.tcl]
set testprefix fts5content

#-------------------------------------------------------------------------
# Contentless tables











>







1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 2014 Dec 20
#
# 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.
#
#***********************************************************************
#
# This file contains tests for the content= and content_rowid= options.
#

source [file join [file dirname [info script]] fts5_common.tcl]
set testprefix fts5content

#-------------------------------------------------------------------------
# Contentless tables
181
182
183
184
185
186
187
188





























189
190


do_execsql_test 3.7 {
  CREATE VIRTUAL TABLE t4 USING fts5(x);
} {}
do_catchsql_test 3.8 {
  INSERT INTO t4(t4) VALUES('delete-all');
} {1 {'delete-all' may only be used with a contentless or external content fts5 table}}






























finish_test










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


>
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
219
220
221

do_execsql_test 3.7 {
  CREATE VIRTUAL TABLE t4 USING fts5(x);
} {}
do_catchsql_test 3.8 {
  INSERT INTO t4(t4) VALUES('delete-all');
} {1 {'delete-all' may only be used with a contentless or external content fts5 table}}

#-------------------------------------------------------------------------
# Test an external content table with a more interesting schema.
#
do_execsql_test 4.1 {
  CREATE TABLE x2(a, "key col" PRIMARY KEY, b, c) WITHOUT ROWID;
  INSERT INTO x2 VALUES('a b',   1, 'c d' , 'e f');
  INSERT INTO x2 VALUES('x y', -40, 'z z' , 'y x');

  CREATE VIRTUAL TABLE t2 USING fts5(a, c, content=x2, content_rowid='key col');
  INSERT INTO t2(t2) VALUES('rebuild');
}

do_execsql_test 4.2 { SELECT rowid FROM t2 } {-40 1}
do_execsql_test 4.3 { SELECT rowid FROM t2 WHERE t2 MATCH 'c'} {}
do_execsql_test 4.4 { SELECT rowid FROM t2 WHERE t2 MATCH 'a'} {1}
do_execsql_test 4.5 { SELECT rowid FROM t2 WHERE t2 MATCH 'x'} {-40}

do_execsql_test 4.6 { INSERT INTO t2(t2) VALUES('integrity-check') } {}

do_execsql_test 4.7 { 
  DELETE FROM x2 WHERE "key col" = 1;
  INSERT INTO t2(t2, rowid, a, c) VALUES('delete', 1, 'a b', 'e f');
  INSERT INTO t2(t2) VALUES('integrity-check');
}

do_execsql_test 4.8 { SELECT rowid FROM t2 WHERE t2 MATCH 'b'} {}
do_execsql_test 4.9 { SELECT rowid FROM t2 WHERE t2 MATCH 'y'} {-40}


finish_test


Changes to ext/fts5/test/fts5fault2.test.
93
94
95
96
97
98
99



















100
101
102
  faultsim_restore_and_reopen
  execsql { SELECT rowid FROM zzz }
} -body {
  execsql { INSERT INTO zzz(zzz) VALUES('optimize') }
} -test {
  faultsim_test_result {0 {}}
}




















finish_test








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



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
  faultsim_restore_and_reopen
  execsql { SELECT rowid FROM zzz }
} -body {
  execsql { INSERT INTO zzz(zzz) VALUES('optimize') }
} -test {
  faultsim_test_result {0 {}}
}

#-------------------------------------------------------------------------
# OOM within an 'integrity-check' operation.
#
reset_db 
db func rnddoc fts5_rnddoc
do_execsql_test 4.0 {
  CREATE VIRTUAL TABLE zzz USING fts5(z);
  INSERT INTO zzz(zzz, rank) VALUES('pgsz', 32);
  WITH ii(i) AS (SELECT 1 UNION SELECT i+1 FROM ii WHERE i<10)
  INSERT INTO zzz SELECT rnddoc(10) || ' xccc' FROM ii;
}

do_faultsim_test 4.1 -faults oom-trans* -prep {
} -body {
  execsql { INSERT INTO zzz(zzz) VALUES('integrity-check') }
} -test {
  faultsim_test_result {0 {}}
}

finish_test

Added ext/fts5/tool/showfts5.tcl.






























































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


proc usage {} {
  puts stderr "usage: $::argv0 database table"
  puts stderr ""
  exit 1
}

set o(vtab)       fts5
set o(tok)        ""
set o(limit)      0
set o(automerge)  -1
set o(crisismerge)  -1

if {[llength $argv]!=2} usage

set database [lindex $argv 0]
set tbl [lindex $argv 1]

sqlite3 db $database

db eval "SELECT fts5_decode(rowid, block) AS d FROM ${tbl}_data WHERE id=10" {
  foreach lvl [lrange $d 1 end] {
    puts $lvl
  }
}