/ Check-in [e38e2bb6]
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 | SQL archive
Timelines: family | ancestors | descendants | both | fts5
Files: files | file ages | folders
SHA1: e38e2bb637844dae8ae5d5f3e23d8369e1b91e45
User & Date: dan 2015-04-27 16:21:49
Context
2015-04-28
18:35
Improve coverage of fts5 tests. check-in: 8e8136f2 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: e38e2bb6 user: dan tags: fts5
11:31
Further tests for fts5. check-in: ffeb3ef3 user: dan tags: fts5
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/fts5/fts5Int.h.

    81     81   **
    82     82   ** And all information loaded from the %_config table.
    83     83   **
    84     84   ** nAutomerge:
    85     85   **   The minimum number of segments that an auto-merge operation should
    86     86   **   attempt to merge together. A value of 1 sets the object to use the 
    87     87   **   compile time default. Zero disables auto-merge altogether.
           88  +**
           89  +** zContent:
           90  +**
           91  +** zContentRowid:
           92  +**   The value of the content_rowid= option, if one was specified. Or 
           93  +**   the string "rowid" otherwise. This text is not quoted - if it is
           94  +**   used as part of an SQL statement it needs to be quoted appropriately.
           95  +**
           96  +** zContentExprlist:
           97  +**
    88     98   */
    89     99   struct Fts5Config {
    90    100     sqlite3 *db;                    /* Database handle */
    91    101     char *zDb;                      /* Database holding FTS index (e.g. "main") */
    92    102     char *zName;                    /* Name of FTS index */
    93    103     int nCol;                       /* Number of columns */
    94    104     char **azCol;                   /* Column names */
    95    105     u8 *abUnindexed;                /* True for unindexed columns */
    96    106     int nPrefix;                    /* Number of prefix indexes */
    97    107     int *aPrefix;                   /* Sizes in bytes of nPrefix prefix indexes */
    98    108     int eContent;                   /* An FTS5_CONTENT value */
    99    109     char *zContent;                 /* content table */ 
   100    110     char *zContentRowid;            /* "content_rowid=" option value */ 
          111  +  char *zContentExprlist;
   101    112     Fts5Tokenizer *pTok;
   102    113     fts5_tokenizer *pTokApi;
   103    114   
   104    115     /* Values loaded from the %_config table */
   105    116     int iCookie;                    /* Incremented when %_config is modified */
   106    117     int pgsz;                       /* Approximate page size used in %_data */
   107    118     int nAutomerge;                 /* 'automerge' setting */

Changes to ext/fts5/fts5_config.c.

   366    366   
   367    367     if( sqlite3_strnicmp("content_rowid", zCmd, nCmd)==0 ){
   368    368       int rc = SQLITE_OK;
   369    369       if( pConfig->zContentRowid ){
   370    370         *pzErr = sqlite3_mprintf("multiple content_rowid=... directives");
   371    371         rc = SQLITE_ERROR;
   372    372       }else{
   373         -      pConfig->zContentRowid = fts5EscapeName(&rc, zArg);
          373  +      pConfig->zContentRowid = fts5Strdup(&rc, zArg);
   374    374       }
   375    375       return rc;
   376    376     }
   377    377   
   378    378     *pzErr = sqlite3_mprintf("unrecognized option: \"%.*s\"", nCmd, zCmd);
   379    379     return SQLITE_ERROR;
   380    380   }
................................................................................
   464    464         rc = SQLITE_ERROR;
   465    465       }
   466    466     }
   467    467   
   468    468     p->azCol[p->nCol++] = zCol;
   469    469     return rc;
   470    470   }
          471  +
          472  +/*
          473  +** Populate the Fts5Config.zContentExprlist string.
          474  +*/
          475  +static int fts5ConfigMakeExprlist(Fts5Config *p){
          476  +  int i;
          477  +  int rc = SQLITE_OK;
          478  +  Fts5Buffer buf = {0, 0, 0};
          479  +  const char *zSep = "";
          480  +
          481  +  sqlite3Fts5BufferAppendPrintf(&rc, &buf, "T.%Q", p->zContentRowid);
          482  +  if( p->eContent!=FTS5_CONTENT_NONE ){
          483  +    for(i=0; i<p->nCol; i++){
          484  +      if( p->eContent==FTS5_CONTENT_EXTERNAL ){
          485  +        sqlite3Fts5BufferAppendPrintf(&rc, &buf, ", T.%Q", p->azCol[i]);
          486  +      }else{
          487  +        sqlite3Fts5BufferAppendPrintf(&rc, &buf, ", T.c%d", i);
          488  +      }
          489  +    }
          490  +  }
          491  +
          492  +  assert( p->zContentExprlist==0 );
          493  +  p->zContentExprlist = (char*)buf.p;
          494  +  return rc;
          495  +}
   471    496   
   472    497   /*
   473    498   ** Arguments nArg/azArg contain the string arguments passed to the xCreate
   474    499   ** or xConnect method of the virtual table. This function attempts to 
   475    500   ** allocate an instance of Fts5Config containing the results of parsing
   476    501   ** those arguments.
   477    502   **
................................................................................
   566    591         sqlite3_free(pRet->zContentRowid);
   567    592         pRet->zContentRowid = 0;
   568    593       }
   569    594     }
   570    595     if( rc==SQLITE_OK && pRet->zContentRowid==0 ){
   571    596       pRet->zContentRowid = fts5Strdup(&rc, "rowid");
   572    597     }
          598  +
          599  +  /* Formulate the zContentExprlist text */
          600  +  if( rc==SQLITE_OK ){
          601  +    rc = fts5ConfigMakeExprlist(pRet);
          602  +  }
   573    603   
   574    604     if( rc!=SQLITE_OK ){
   575    605       sqlite3Fts5ConfigFree(pRet);
   576    606       *ppOut = 0;
   577    607     }
   578    608     return rc;
   579    609   }
................................................................................
   594    624       }
   595    625       sqlite3_free(pConfig->azCol);
   596    626       sqlite3_free(pConfig->aPrefix);
   597    627       sqlite3_free(pConfig->zRank);
   598    628       sqlite3_free(pConfig->zRankArgs);
   599    629       sqlite3_free(pConfig->zContent);
   600    630       sqlite3_free(pConfig->zContentRowid);
          631  +    sqlite3_free(pConfig->zContentExprlist);
   601    632       sqlite3_free(pConfig);
   602    633     }
   603    634   }
   604    635   
   605    636   /*
   606    637   ** Call sqlite3_declare_vtab() based on the contents of the configuration
   607    638   ** object passed as the only argument. Return SQLITE_OK if successful, or

Changes to ext/fts5/fts5_index.c.

  4725   4725   static void fts5IndexIntegrityCheckSegment(
  4726   4726     Fts5Index *p,                   /* FTS5 backend object */
  4727   4727     int iIdx,                       /* Index that pSeg is a part of */
  4728   4728     Fts5StructureSegment *pSeg      /* Segment to check internal consistency */
  4729   4729   ){
  4730   4730     Fts5BtreeIter iter;             /* Used to iterate through b-tree hierarchy */
  4731   4731   
  4732         -  if( pSeg->pgnoFirst==0 && pSeg->pgnoLast==0 ) return;
         4732  +  if( pSeg->pgnoFirst==0 ) return;
  4733   4733   
  4734   4734     /* Iterate through the b-tree hierarchy.  */
  4735   4735     for(fts5BtreeIterInit(p, iIdx, pSeg, &iter);
  4736   4736         p->rc==SQLITE_OK && iter.bEof==0;
  4737   4737         fts5BtreeIterNext(&iter)
  4738   4738     ){
  4739   4739       i64 iRow;                     /* Rowid for this leaf */
................................................................................
  5144   5144   **
  5145   5145   ** The return value is the number of bytes read from the input buffer.
  5146   5146   */
  5147   5147   static int fts5DecodeDoclist(int *pRc, Fts5Buffer *pBuf, const u8 *a, int n){
  5148   5148     i64 iDocid;
  5149   5149     int iOff = 0;
  5150   5150   
  5151         -  if( iOff<n ){
  5152         -    iOff += sqlite3GetVarint(&a[iOff], (u64*)&iDocid);
  5153         -    sqlite3Fts5BufferAppendPrintf(pRc, pBuf, " rowid=%lld", iDocid);
  5154         -  }
         5151  +  iOff = sqlite3GetVarint(&a[iOff], (u64*)&iDocid);
         5152  +  sqlite3Fts5BufferAppendPrintf(pRc, pBuf, " rowid=%lld", iDocid);
  5155   5153     while( iOff<n ){
  5156   5154       int nPos;
  5157   5155       int bDummy;
  5158   5156       iOff += fts5GetPoslistSize(&a[iOff], &nPos, &bDummy);
  5159   5157       iOff += fts5DecodePoslist(pRc, pBuf, &a[iOff], MIN(n-iOff, nPos));
  5160   5158       if( iOff<n ){
  5161   5159         i64 iDelta;

Changes to ext/fts5/fts5_storage.c.

    61     61     char **pzErrMsg                 /* OUT: Error message (if any) */
    62     62   ){
    63     63     int rc = SQLITE_OK;
    64     64   
    65     65     assert( eStmt>=0 && eStmt<ArraySize(p->aStmt) );
    66     66     if( p->aStmt[eStmt]==0 ){
    67     67       const char *azStmt[] = {
    68         -      "SELECT * FROM %s ORDER BY %s ASC",               /* SCAN_ASC */
    69         -      "SELECT * FROM %s ORDER BY %s DESC",              /* SCAN_DESC */
    70         -      "SELECT * FROM %s WHERE %s=?",                    /* LOOKUP  */
           68  +      "SELECT %s FROM %s T ORDER BY T.%Q ASC",          /* SCAN_ASC */
           69  +      "SELECT %s FROM %s T ORDER BY T.%Q DESC",         /* SCAN_DESC */
           70  +      "SELECT %s FROM %s T WHERE    T.%Q=?",            /* LOOKUP  */
    71     71   
    72     72         "INSERT INTO %Q.'%q_content' VALUES(%s)",         /* INSERT_CONTENT  */
    73     73         "REPLACE INTO %Q.'%q_content' VALUES(%s)",        /* REPLACE_CONTENT */
    74     74         "DELETE FROM %Q.'%q_content' WHERE id=?",         /* DELETE_CONTENT  */
    75     75         "REPLACE INTO %Q.'%q_docsize' VALUES(?,?)",       /* REPLACE_DOCSIZE  */
    76     76         "DELETE FROM %Q.'%q_docsize' WHERE id=?",         /* DELETE_DOCSIZE  */
    77     77   
................................................................................
    82     82       Fts5Config *pC = p->pConfig;
    83     83       char *zSql = 0;
    84     84   
    85     85       switch( eStmt ){
    86     86         case FTS5_STMT_SCAN_ASC:
    87     87         case FTS5_STMT_SCAN_DESC:
    88     88         case FTS5_STMT_LOOKUP:
    89         -        zSql = sqlite3_mprintf(azStmt[eStmt], pC->zContent, pC->zContentRowid);
           89  +        zSql = sqlite3_mprintf(azStmt[eStmt], 
           90  +            pC->zContentExprlist, pC->zContent, pC->zContentRowid
           91  +        );
    90     92           break;
    91     93   
    92     94         case FTS5_STMT_INSERT_CONTENT: 
    93     95         case FTS5_STMT_REPLACE_CONTENT: {
    94     96           int nCol = pC->nCol + 1;
    95     97           char *zBind;
    96     98           int i;

Changes to ext/fts5/test/fts5content.test.

     5      5   #
     6      6   #    May you do good and not evil.
     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   #
           12  +# This file contains tests for the content= and content_rowid= options.
    12     13   #
    13     14   
    14     15   source [file join [file dirname [info script]] fts5_common.tcl]
    15     16   set testprefix fts5content
    16     17   
    17     18   #-------------------------------------------------------------------------
    18     19   # Contentless tables
................................................................................
   181    182   
   182    183   do_execsql_test 3.7 {
   183    184     CREATE VIRTUAL TABLE t4 USING fts5(x);
   184    185   } {}
   185    186   do_catchsql_test 3.8 {
   186    187     INSERT INTO t4(t4) VALUES('delete-all');
   187    188   } {1 {'delete-all' may only be used with a contentless or external content fts5 table}}
          189  +
          190  +#-------------------------------------------------------------------------
          191  +# Test an external content table with a more interesting schema.
          192  +#
          193  +do_execsql_test 4.1 {
          194  +  CREATE TABLE x2(a, "key col" PRIMARY KEY, b, c) WITHOUT ROWID;
          195  +  INSERT INTO x2 VALUES('a b',   1, 'c d' , 'e f');
          196  +  INSERT INTO x2 VALUES('x y', -40, 'z z' , 'y x');
          197  +
          198  +  CREATE VIRTUAL TABLE t2 USING fts5(a, c, content=x2, content_rowid='key col');
          199  +  INSERT INTO t2(t2) VALUES('rebuild');
          200  +}
          201  +
          202  +do_execsql_test 4.2 { SELECT rowid FROM t2 } {-40 1}
          203  +do_execsql_test 4.3 { SELECT rowid FROM t2 WHERE t2 MATCH 'c'} {}
          204  +do_execsql_test 4.4 { SELECT rowid FROM t2 WHERE t2 MATCH 'a'} {1}
          205  +do_execsql_test 4.5 { SELECT rowid FROM t2 WHERE t2 MATCH 'x'} {-40}
          206  +
          207  +do_execsql_test 4.6 { INSERT INTO t2(t2) VALUES('integrity-check') } {}
          208  +
          209  +do_execsql_test 4.7 { 
          210  +  DELETE FROM x2 WHERE "key col" = 1;
          211  +  INSERT INTO t2(t2, rowid, a, c) VALUES('delete', 1, 'a b', 'e f');
          212  +  INSERT INTO t2(t2) VALUES('integrity-check');
          213  +}
          214  +
          215  +do_execsql_test 4.8 { SELECT rowid FROM t2 WHERE t2 MATCH 'b'} {}
          216  +do_execsql_test 4.9 { SELECT rowid FROM t2 WHERE t2 MATCH 'y'} {-40}
          217  +
   188    218   
   189    219   finish_test
          220  +
   190    221   

Changes to ext/fts5/test/fts5fault2.test.

    93     93     faultsim_restore_and_reopen
    94     94     execsql { SELECT rowid FROM zzz }
    95     95   } -body {
    96     96     execsql { INSERT INTO zzz(zzz) VALUES('optimize') }
    97     97   } -test {
    98     98     faultsim_test_result {0 {}}
    99     99   }
          100  +
          101  +#-------------------------------------------------------------------------
          102  +# OOM within an 'integrity-check' operation.
          103  +#
          104  +reset_db 
          105  +db func rnddoc fts5_rnddoc
          106  +do_execsql_test 4.0 {
          107  +  CREATE VIRTUAL TABLE zzz USING fts5(z);
          108  +  INSERT INTO zzz(zzz, rank) VALUES('pgsz', 32);
          109  +  WITH ii(i) AS (SELECT 1 UNION SELECT i+1 FROM ii WHERE i<10)
          110  +  INSERT INTO zzz SELECT rnddoc(10) || ' xccc' FROM ii;
          111  +}
          112  +
          113  +do_faultsim_test 4.1 -faults oom-trans* -prep {
          114  +} -body {
          115  +  execsql { INSERT INTO zzz(zzz) VALUES('integrity-check') }
          116  +} -test {
          117  +  faultsim_test_result {0 {}}
          118  +}
   100    119   
   101    120   finish_test
   102    121   

Added ext/fts5/tool/showfts5.tcl.

            1  +
            2  +
            3  +proc usage {} {
            4  +  puts stderr "usage: $::argv0 database table"
            5  +  puts stderr ""
            6  +  exit 1
            7  +}
            8  +
            9  +set o(vtab)       fts5
           10  +set o(tok)        ""
           11  +set o(limit)      0
           12  +set o(automerge)  -1
           13  +set o(crisismerge)  -1
           14  +
           15  +if {[llength $argv]!=2} usage
           16  +
           17  +set database [lindex $argv 0]
           18  +set tbl [lindex $argv 1]
           19  +
           20  +sqlite3 db $database
           21  +
           22  +db eval "SELECT fts5_decode(rowid, block) AS d FROM ${tbl}_data WHERE id=10" {
           23  +  foreach lvl [lrange $d 1 end] {
           24  +    puts $lvl
           25  +  }
           26  +}
           27  +
           28  +
           29  +
           30  +
           31  +