/ Check-in [1d27ea74]
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 experimental 'content' option to FTS4.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | fts4-content
Files: files | file ages | folders
SHA1: 1d27ea741f61c624e18bdc6a3b1c2d8574a64ddc
User & Date: dan 2011-10-04 11:22:59
Context
2011-10-04
16:37
Add tests to check that modifying the schema of an FTS content table does not cause a crash in the FTS module. Also disable the deferred token optimization for content=xxx FTS tables. check-in: be86c706 user: dan tags: fts4-content
11:22
Add experimental 'content' option to FTS4. check-in: 1d27ea74 user: dan tags: fts4-content
2011-10-02
05:23
Update MSVC makefile to allow targets to be built with support for ICU. check-in: eb5da5e1 user: mistachkin tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/fts3/fts3.c.

   464    464     /* Free any prepared statements held */
   465    465     for(i=0; i<SizeofArray(p->aStmt); i++){
   466    466       sqlite3_finalize(p->aStmt[i]);
   467    467     }
   468    468     sqlite3_free(p->zSegmentsTbl);
   469    469     sqlite3_free(p->zReadExprlist);
   470    470     sqlite3_free(p->zWriteExprlist);
          471  +  sqlite3_free(p->zContentTbl);
   471    472   
   472    473     /* Invoke the tokenizer destructor to free the tokenizer. */
   473    474     p->pTokenizer->pModule->xDestroy(p->pTokenizer);
   474    475   
   475    476     sqlite3_free(p);
   476    477     return SQLITE_OK;
   477    478   }
................................................................................
   503    504     }
   504    505   }
   505    506   
   506    507   /*
   507    508   ** The xDestroy() virtual table method.
   508    509   */
   509    510   static int fts3DestroyMethod(sqlite3_vtab *pVtab){
   510         -  int rc = SQLITE_OK;              /* Return code */
   511    511     Fts3Table *p = (Fts3Table *)pVtab;
   512         -  sqlite3 *db = p->db;
          512  +  int rc = SQLITE_OK;              /* Return code */
          513  +  const char *zDb = p->zDb;        /* Name of database (e.g. "main", "temp") */
          514  +  sqlite3 *db = p->db;             /* Database handle */
   513    515   
   514    516     /* Drop the shadow tables */
   515         -  fts3DbExec(&rc, db, "DROP TABLE IF EXISTS %Q.'%q_content'", p->zDb, p->zName);
   516         -  fts3DbExec(&rc, db, "DROP TABLE IF EXISTS %Q.'%q_segments'", p->zDb,p->zName);
   517         -  fts3DbExec(&rc, db, "DROP TABLE IF EXISTS %Q.'%q_segdir'", p->zDb, p->zName);
   518         -  fts3DbExec(&rc, db, "DROP TABLE IF EXISTS %Q.'%q_docsize'", p->zDb, p->zName);
   519         -  fts3DbExec(&rc, db, "DROP TABLE IF EXISTS %Q.'%q_stat'", p->zDb, p->zName);
          517  +  if( p->zContentTbl==0 ){
          518  +    fts3DbExec(&rc, db, "DROP TABLE IF EXISTS %Q.'%q_content'", zDb, p->zName);
          519  +  }
          520  +  fts3DbExec(&rc, db, "DROP TABLE IF EXISTS %Q.'%q_segments'", zDb,p->zName);
          521  +  fts3DbExec(&rc, db, "DROP TABLE IF EXISTS %Q.'%q_segdir'", zDb, p->zName);
          522  +  fts3DbExec(&rc, db, "DROP TABLE IF EXISTS %Q.'%q_docsize'", zDb, p->zName);
          523  +  fts3DbExec(&rc, db, "DROP TABLE IF EXISTS %Q.'%q_stat'", zDb, p->zName);
   520    524   
   521    525     /* If everything has worked, invoke fts3DisconnectMethod() to free the
   522    526     ** memory associated with the Fts3Table structure and return SQLITE_OK.
   523    527     ** Otherwise, return an SQLite error code.
   524    528     */
   525    529     return (rc==SQLITE_OK ? fts3DisconnectMethod(pVtab) : rc);
   526    530   }
................................................................................
   574    578   ** If the p->bHasDocsize boolean is true (indicating that this is an
   575    579   ** FTS4 table, not an FTS3 table) then also create the %_docsize and
   576    580   ** %_stat tables required by FTS4.
   577    581   */
   578    582   static int fts3CreateTables(Fts3Table *p){
   579    583     int rc = SQLITE_OK;             /* Return code */
   580    584     int i;                          /* Iterator variable */
   581         -  char *zContentCols;             /* Columns of %_content table */
   582    585     sqlite3 *db = p->db;            /* The database connection */
   583    586   
   584         -  /* Create a list of user columns for the content table */
   585         -  zContentCols = sqlite3_mprintf("docid INTEGER PRIMARY KEY");
   586         -  for(i=0; zContentCols && i<p->nColumn; i++){
   587         -    char *z = p->azColumn[i];
   588         -    zContentCols = sqlite3_mprintf("%z, 'c%d%q'", zContentCols, i, z);
          587  +  if( p->zContentTbl==0 ){
          588  +    char *zContentCols;           /* Columns of %_content table */
          589  +
          590  +    /* Create a list of user columns for the content table */
          591  +    zContentCols = sqlite3_mprintf("docid INTEGER PRIMARY KEY");
          592  +    for(i=0; zContentCols && i<p->nColumn; i++){
          593  +      char *z = p->azColumn[i];
          594  +      zContentCols = sqlite3_mprintf("%z, 'c%d%q'", zContentCols, i, z);
          595  +    }
          596  +    if( zContentCols==0 ) rc = SQLITE_NOMEM;
          597  +  
          598  +    /* Create the content table */
          599  +    fts3DbExec(&rc, db, 
          600  +       "CREATE TABLE %Q.'%q_content'(%s)",
          601  +       p->zDb, p->zName, zContentCols
          602  +    );
          603  +    sqlite3_free(zContentCols);
   589    604     }
   590         -  if( zContentCols==0 ) rc = SQLITE_NOMEM;
   591    605   
   592         -  /* Create the content table */
   593         -  fts3DbExec(&rc, db, 
   594         -     "CREATE TABLE %Q.'%q_content'(%s)",
   595         -     p->zDb, p->zName, zContentCols
   596         -  );
   597         -  sqlite3_free(zContentCols);
   598    606     /* Create other tables */
   599    607     fts3DbExec(&rc, db, 
   600    608         "CREATE TABLE %Q.'%q_segments'(blockid INTEGER PRIMARY KEY, block BLOB);",
   601    609         p->zDb, p->zName
   602    610     );
   603    611     fts3DbExec(&rc, db, 
   604    612         "CREATE TABLE %Q.'%q_segdir'("
................................................................................
   741    749       *(z++) = '"';
   742    750       *(z++) = '\0';
   743    751     }
   744    752     return zRet;
   745    753   }
   746    754   
   747    755   /*
   748         -** Return a list of comma separated SQL expressions that could be used
   749         -** in a SELECT statement such as the following:
          756  +** Return a list of comma separated SQL expressions and a FROM clause that 
          757  +** could be used in a SELECT statement such as the following:
   750    758   **
   751    759   **     SELECT <list of expressions> FROM %_content AS x ...
   752    760   **
   753    761   ** to return the docid, followed by each column of text data in order
   754    762   ** from left to write. If parameter zFunc is not NULL, then instead of
   755    763   ** being returned directly each column of text data is passed to an SQL
   756    764   ** function named zFunc first. For example, if zFunc is "unzip" and the
   757    765   ** table has the three user-defined columns "a", "b", and "c", the following
   758    766   ** string is returned:
   759    767   **
   760         -**     "docid, unzip(x.'a'), unzip(x.'b'), unzip(x.'c')"
          768  +**     "docid, unzip(x.'a'), unzip(x.'b'), unzip(x.'c') FROM %_content AS x"
   761    769   **
   762    770   ** The pointer returned points to a buffer allocated by sqlite3_malloc(). It
   763    771   ** is the responsibility of the caller to eventually free it.
   764    772   **
   765    773   ** If *pRc is not SQLITE_OK when this function is called, it is a no-op (and
   766    774   ** a NULL pointer is returned). Otherwise, if an OOM error is encountered
   767    775   ** by this function, NULL is returned and *pRc is set to SQLITE_NOMEM. If
................................................................................
   769    777   */
   770    778   static char *fts3ReadExprList(Fts3Table *p, const char *zFunc, int *pRc){
   771    779     char *zRet = 0;
   772    780     char *zFree = 0;
   773    781     char *zFunction;
   774    782     int i;
   775    783   
   776         -  if( !zFunc ){
   777         -    zFunction = "";
          784  +  if( p->zContentTbl==0 ){
          785  +    if( !zFunc ){
          786  +      zFunction = "";
          787  +    }else{
          788  +      zFree = zFunction = fts3QuoteId(zFunc);
          789  +    }
          790  +    fts3Appendf(pRc, &zRet, "docid");
          791  +    for(i=0; i<p->nColumn; i++){
          792  +      fts3Appendf(pRc, &zRet, ",%s(x.'c%d%q')", zFunction, i, p->azColumn[i]);
          793  +    }
          794  +    sqlite3_free(zFree);
   778    795     }else{
   779         -    zFree = zFunction = fts3QuoteId(zFunc);
          796  +    fts3Appendf(pRc, &zRet, "rowid");
          797  +    for(i=0; i<p->nColumn; i++){
          798  +      fts3Appendf(pRc, &zRet, ", x.'%q'", p->azColumn[i]);
          799  +    }
   780    800     }
   781         -  fts3Appendf(pRc, &zRet, "docid");
   782         -  for(i=0; i<p->nColumn; i++){
   783         -    fts3Appendf(pRc, &zRet, ",%s(x.'c%d%q')", zFunction, i, p->azColumn[i]);
   784         -  }
   785         -  sqlite3_free(zFree);
          801  +  fts3Appendf(pRc, &zRet, "FROM '%q'.'%q%s' AS x", 
          802  +      p->zDb,
          803  +      (p->zContentTbl ? p->zContentTbl : p->zName),
          804  +      (p->zContentTbl ? "" : "_content")
          805  +  );
   786    806     return zRet;
   787    807   }
   788    808   
   789    809   /*
   790    810   ** Return a list of N comma separated question marks, where N is the number
   791    811   ** of columns in the %_content table (one for the docid plus one for each
   792    812   ** user-defined text column).
................................................................................
   901    921         aIndex[i].nPrefix = nPrefix;
   902    922         p++;
   903    923       }
   904    924     }
   905    925   
   906    926     return SQLITE_OK;
   907    927   }
          928  +
          929  +/*
          930  +** This function is called when initializing an FTS4 table that uses the
          931  +** content=xxx option. It determines the number of and names of the columns
          932  +** of the new FTS4 table.
          933  +**
          934  +** The third argument passed to this function is the value passed to the
          935  +** config=xxx option (i.e. "xxx"). This function queries the database for
          936  +** a table of that name. If found, the output variables are populated
          937  +** as follows:
          938  +**
          939  +**   *pnCol:   Set to the number of columns table xxx has,
          940  +**
          941  +**   *pnStr:   Set to the total amount of space required to store a copy
          942  +**             of each columns name, including the nul-terminator.
          943  +**
          944  +**   *pazCol:  Set to point to an array of *pnCol strings. Each string is
          945  +**             the name of the corresponding column in table xxx. The array
          946  +**             and its contents are allocated using a single allocation. It
          947  +**             is the responsibility of the caller to free this allocation
          948  +**             by eventually passing the *pazCol value to sqlite3_free().
          949  +**
          950  +** If the table cannot be found, an error code is returned and the output
          951  +** variables are undefined. Or, if an OOM is encountered, SQLITE_NOMEM is
          952  +** returned (and the output variables are undefined).
          953  +*/
          954  +static int fts3ContentColumns(
          955  +  sqlite3 *db,                    /* Database handle */
          956  +  const char *zDb,                /* Name of db (i.e. "main", "temp" etc.) */
          957  +  const char *zTbl,               /* Name of content table */
          958  +  const char ***pazCol,           /* OUT: Malloc'd array of column names */
          959  +  int *pnCol,                     /* OUT: Size of array *pazCol */
          960  +  int *pnStr                      /* OUT: Bytes of string content */
          961  +){
          962  +  int rc = SQLITE_OK;             /* Return code */
          963  +  char *zSql;                     /* "SELECT *" statement on zTbl */  
          964  +  sqlite3_stmt *pStmt = 0;        /* Compiled version of zSql */
          965  +
          966  +  zSql = sqlite3_mprintf("SELECT * FROM %Q.%Q", zDb, zTbl);
          967  +  if( !zSql ){
          968  +    rc = SQLITE_NOMEM;
          969  +  }else{
          970  +    rc = sqlite3_prepare(db, zSql, -1, &pStmt, 0);
          971  +  }
          972  +  sqlite3_free(zSql);
          973  +
          974  +  if( rc==SQLITE_OK ){
          975  +    const char **azCol;           /* Output array */
          976  +    int nStr = 0;                 /* Size of all column names (incl. 0x00) */
          977  +    int nCol;                     /* Number of table columns */
          978  +    int i;                        /* Used to iterate through columns */
          979  +
          980  +    /* Loop through the returned columns. Set nStr to the number of bytes of
          981  +    ** space required to store a copy of each column name, including the
          982  +    ** nul-terminator byte.  */
          983  +    nCol = sqlite3_column_count(pStmt);
          984  +    for(i=0; i<nCol; i++){
          985  +      const char *zCol = sqlite3_column_name(pStmt, i);
          986  +      nStr += strlen(zCol) + 1;
          987  +    }
          988  +
          989  +    /* Allocate and populate the array to return. */
          990  +    azCol = (const char **)sqlite3_malloc(sizeof(char *) * nCol + nStr);
          991  +    if( azCol==0 ){
          992  +      rc = SQLITE_NOMEM;
          993  +    }else{
          994  +      char *p = (char *)&azCol[nCol];
          995  +      for(i=0; i<nCol; i++){
          996  +        const char *zCol = sqlite3_column_name(pStmt, i);
          997  +        int n = strlen(zCol)+1;
          998  +        memcpy(p, zCol, n);
          999  +        azCol[i] = p;
         1000  +        p += n;
         1001  +      }
         1002  +    }
         1003  +    sqlite3_finalize(pStmt);
         1004  +
         1005  +    /* Set the output variables. */
         1006  +    *pnCol = nCol;
         1007  +    *pnStr = nStr;
         1008  +    *pazCol = azCol;
         1009  +  }
         1010  +
         1011  +  return rc;
         1012  +}
   908   1013   
   909   1014   /*
   910   1015   ** This function is the implementation of both the xConnect and xCreate
   911   1016   ** methods of the FTS3 virtual table.
   912   1017   **
   913   1018   ** The argv[] array contains the following:
   914   1019   **
................................................................................
   946   1051   
   947   1052     /* The results of parsing supported FTS4 key=value options: */
   948   1053     int bNoDocsize = 0;             /* True to omit %_docsize table */
   949   1054     int bDescIdx = 0;               /* True to store descending indexes */
   950   1055     char *zPrefix = 0;              /* Prefix parameter value (or NULL) */
   951   1056     char *zCompress = 0;            /* compress=? parameter (or NULL) */
   952   1057     char *zUncompress = 0;          /* uncompress=? parameter (or NULL) */
         1058  +  char *zContent = 0;             /* content=? parameter (or NULL) */
   953   1059   
   954   1060     assert( strlen(argv[0])==4 );
   955   1061     assert( (sqlite3_strnicmp(argv[0], "fts4", 4)==0 && isFts4)
   956   1062          || (sqlite3_strnicmp(argv[0], "fts3", 4)==0 && !isFts4)
   957   1063     );
   958   1064   
   959   1065     nDb = (int)strlen(argv[1]) + 1;
................................................................................
   989   1095       }
   990   1096   
   991   1097       /* Check if it is an FTS4 special argument. */
   992   1098       else if( isFts4 && fts3IsSpecialColumn(z, &nKey, &zVal) ){
   993   1099         struct Fts4Option {
   994   1100           const char *zOpt;
   995   1101           int nOpt;
   996         -        char **pzVar;
   997   1102         } aFts4Opt[] = {
   998         -        { "matchinfo",   9, 0 },            /* 0 -> MATCHINFO */
   999         -        { "prefix",      6, 0 },            /* 1 -> PREFIX */
  1000         -        { "compress",    8, 0 },            /* 2 -> COMPRESS */
  1001         -        { "uncompress", 10, 0 },            /* 3 -> UNCOMPRESS */
  1002         -        { "order",       5, 0 }             /* 4 -> ORDER */
         1103  +        { "matchinfo",   9 },     /* 0 -> MATCHINFO */
         1104  +        { "prefix",      6 },     /* 1 -> PREFIX */
         1105  +        { "compress",    8 },     /* 2 -> COMPRESS */
         1106  +        { "uncompress", 10 },     /* 3 -> UNCOMPRESS */
         1107  +        { "order",       5 },     /* 4 -> ORDER */
         1108  +        { "content",     7 }      /* 5 -> CONTENT */
  1003   1109         };
  1004   1110   
  1005   1111         int iOpt;
  1006   1112         if( !zVal ){
  1007   1113           rc = SQLITE_NOMEM;
  1008   1114         }else{
  1009   1115           for(iOpt=0; iOpt<SizeofArray(aFts4Opt); iOpt++){
................................................................................
  1048   1154                  && (strlen(zVal)!=4 || sqlite3_strnicmp(zVal, "desc", 3)) 
  1049   1155                 ){
  1050   1156                   *pzErr = sqlite3_mprintf("unrecognized order: %s", zVal);
  1051   1157                   rc = SQLITE_ERROR;
  1052   1158                 }
  1053   1159                 bDescIdx = (zVal[0]=='d' || zVal[0]=='D');
  1054   1160                 break;
         1161  +
         1162  +            case 5:               /* CONTENT */
         1163  +              sqlite3_free(zUncompress);
         1164  +              zContent = zVal;
         1165  +              zVal = 0;
         1166  +              break;
  1055   1167             }
  1056   1168           }
  1057   1169           sqlite3_free(zVal);
  1058   1170         }
  1059   1171       }
  1060   1172   
  1061   1173       /* Otherwise, the argument is a column name. */
  1062   1174       else {
  1063   1175         nString += (int)(strlen(z) + 1);
  1064   1176         aCol[nCol++] = z;
  1065   1177       }
  1066   1178     }
         1179  +
         1180  +  /* If a content=xxx option was specified, the following:
         1181  +  **
         1182  +  **   1. Ignore any compress= and uncompress= options.
         1183  +  **
         1184  +  **   2. Ignore any column names that were specified as part of the 
         1185  +  **      the CREATE VIRTUAL TABLE statement.
         1186  +  **
         1187  +  **   3. Determine the actual column names to use for the FTS table 
         1188  +  **      based on the columns of the content= table.
         1189  +  */
         1190  +  if( rc==SQLITE_OK && zContent ){
         1191  +    sqlite3_free(aCol); 
         1192  +    sqlite3_free(zCompress); 
         1193  +    sqlite3_free(zUncompress); 
         1194  +    zCompress = 0;
         1195  +    zUncompress = 0;
         1196  +    aCol = 0;
         1197  +    rc = fts3ContentColumns(db, argv[1], zContent, &aCol, &nCol, &nString);
         1198  +    assert( rc!=SQLITE_OK || nCol>0 );
         1199  +  }
  1067   1200     if( rc!=SQLITE_OK ) goto fts3_init_out;
  1068   1201   
  1069   1202     if( nCol==0 ){
  1070   1203       assert( nString==0 );
  1071   1204       aCol[0] = "content";
  1072   1205       nString = 8;
  1073   1206       nCol = 1;
................................................................................
  1104   1237     p->nPendingData = 0;
  1105   1238     p->azColumn = (char **)&p[1];
  1106   1239     p->pTokenizer = pTokenizer;
  1107   1240     p->nMaxPendingData = FTS3_MAX_PENDING_DATA;
  1108   1241     p->bHasDocsize = (isFts4 && bNoDocsize==0);
  1109   1242     p->bHasStat = isFts4;
  1110   1243     p->bDescIdx = bDescIdx;
         1244  +  p->zContentTbl = zContent;
         1245  +  zContent = 0;
  1111   1246     TESTONLY( p->inTransaction = -1 );
  1112   1247     TESTONLY( p->mxSavepoint = -1 );
  1113   1248   
  1114   1249     p->aIndex = (struct Fts3Index *)&p->azColumn[nCol];
  1115   1250     memcpy(p->aIndex, aIndex, sizeof(struct Fts3Index) * nIndex);
  1116   1251     p->nIndex = nIndex;
  1117   1252     for(i=0; i<nIndex; i++){
................................................................................
  1165   1300     fts3DeclareVtab(&rc, p);
  1166   1301   
  1167   1302   fts3_init_out:
  1168   1303     sqlite3_free(zPrefix);
  1169   1304     sqlite3_free(aIndex);
  1170   1305     sqlite3_free(zCompress);
  1171   1306     sqlite3_free(zUncompress);
         1307  +  sqlite3_free(zContent);
  1172   1308     sqlite3_free((void *)aCol);
  1173   1309     if( rc!=SQLITE_OK ){
  1174   1310       if( p ){
  1175   1311         fts3DisconnectMethod((sqlite3_vtab *)p);
  1176   1312       }else if( pTokenizer ){
  1177   1313         pTokenizer->pModule->xDestroy(pTokenizer);
  1178   1314       }
................................................................................
  1330   1466       sqlite3_bind_int64(pCsr->pStmt, 1, pCsr->iPrevId);
  1331   1467       pCsr->isRequireSeek = 0;
  1332   1468       if( SQLITE_ROW==sqlite3_step(pCsr->pStmt) ){
  1333   1469         return SQLITE_OK;
  1334   1470       }else{
  1335   1471         int rc = sqlite3_reset(pCsr->pStmt);
  1336   1472         if( rc==SQLITE_OK ){
  1337         -        /* If no row was found and no error has occured, then the %_content
  1338         -        ** table is missing a row that is present in the full-text index.
  1339         -        ** The data structures are corrupt.
  1340         -        */
  1341         -        rc = SQLITE_CORRUPT_VTAB;
         1473  +        Fts3Table *p = (Fts3Table *)pCsr->base.pVtab;
         1474  +        if( p->zContentTbl==0 ){
         1475  +          /* If no row was found and no error has occured, then the %_content
         1476  +          ** table is missing a row that is present in the full-text index.
         1477  +          ** The data structures are corrupt.
         1478  +          */
         1479  +          rc = SQLITE_CORRUPT_VTAB;
         1480  +        }else{
         1481  +          return SQLITE_OK;
         1482  +        }
  1342   1483         }
  1343   1484         pCsr->isEof = 1;
  1344   1485         if( pContext ){
  1345   1486           sqlite3_result_error_code(pContext, rc);
  1346   1487         }
  1347   1488         return rc;
  1348   1489       }
................................................................................
  2709   2850   
  2710   2851     /* Compile a SELECT statement for this cursor. For a full-table-scan, the
  2711   2852     ** statement loops through all rows of the %_content table. For a
  2712   2853     ** full-text query or docid lookup, the statement retrieves a single
  2713   2854     ** row by docid.
  2714   2855     */
  2715   2856     if( idxNum==FTS3_FULLSCAN_SEARCH ){
  2716         -    const char *zSort = (pCsr->bDesc ? "DESC" : "ASC");
  2717         -    const char *zTmpl = "SELECT %s FROM %Q.'%q_content' AS x ORDER BY docid %s";
  2718         -    zSql = sqlite3_mprintf(zTmpl, p->zReadExprlist, p->zDb, p->zName, zSort);
         2857  +    const char *zTmpl = "SELECT %s ORDER BY rowid %s";
         2858  +    zSql = sqlite3_mprintf(zTmpl, 
         2859  +        p->zReadExprlist, (pCsr->bDesc ? "DESC" : "ASC")
         2860  +    );
  2719   2861     }else{
  2720         -    const char *zTmpl = "SELECT %s FROM %Q.'%q_content' AS x WHERE docid = ?";
  2721         -    zSql = sqlite3_mprintf(zTmpl, p->zReadExprlist, p->zDb, p->zName);
         2862  +    const char *zTmpl = "SELECT %s WHERE rowid = ?";
         2863  +    zSql = sqlite3_mprintf(zTmpl, p->zReadExprlist);
  2722   2864     }
  2723   2865     if( !zSql ) return SQLITE_NOMEM;
  2724   2866     rc = sqlite3_prepare_v2(p->db, zSql, -1, &pCsr->pStmt, 0);
  2725   2867     sqlite3_free(zSql);
  2726   2868     if( rc!=SQLITE_OK ) return rc;
  2727   2869   
  2728   2870     if( idxNum==FTS3_DOCID_SEARCH ){
................................................................................
  2777   2919     }else if( iCol==p->nColumn ){
  2778   2920       /* The extra column whose name is the same as the table.
  2779   2921       ** Return a blob which is a pointer to the cursor.
  2780   2922       */
  2781   2923       sqlite3_result_blob(pContext, &pCsr, sizeof(pCsr), SQLITE_TRANSIENT);
  2782   2924     }else{
  2783   2925       rc = fts3CursorSeek(0, pCsr);
  2784         -    if( rc==SQLITE_OK ){
         2926  +    if( rc==SQLITE_OK && sqlite3_data_count(pCsr->pStmt)>(iCol+1) ){
  2785   2927         sqlite3_result_value(pContext, sqlite3_column_value(pCsr->pStmt, iCol+1));
  2786   2928       }
  2787   2929     }
  2788   2930   
  2789   2931     assert( ((Fts3Table *)pCsr->base.pVtab)->pSegments==0 );
  2790   2932     return rc;
  2791   2933   }
................................................................................
  3075   3217     int rc;                         /* Return Code */
  3076   3218   
  3077   3219     rc = sqlite3Fts3PendingTermsFlush(p);
  3078   3220     if( rc!=SQLITE_OK ){
  3079   3221       return rc;
  3080   3222     }
  3081   3223   
  3082         -  fts3DbExec(&rc, db,
  3083         -    "ALTER TABLE %Q.'%q_content'  RENAME TO '%q_content';",
  3084         -    p->zDb, p->zName, zName
  3085         -  );
         3224  +  if( p->zContentTbl==0 ){
         3225  +    fts3DbExec(&rc, db,
         3226  +      "ALTER TABLE %Q.'%q_content'  RENAME TO '%q_content';",
         3227  +      p->zDb, p->zName, zName
         3228  +    );
         3229  +  }
         3230  +
  3086   3231     if( p->bHasDocsize ){
  3087   3232       fts3DbExec(&rc, db,
  3088   3233         "ALTER TABLE %Q.'%q_docsize'  RENAME TO '%q_docsize';",
  3089   3234         p->zDb, p->zName, zName
  3090   3235       );
  3091   3236     }
  3092   3237     if( p->bHasStat ){

Changes to ext/fts3/fts3Int.h.

   180    180     sqlite3_vtab base;              /* Base class used by SQLite core */
   181    181     sqlite3 *db;                    /* The database connection */
   182    182     const char *zDb;                /* logical database name */
   183    183     const char *zName;              /* virtual table name */
   184    184     int nColumn;                    /* number of named columns in virtual table */
   185    185     char **azColumn;                /* column names.  malloced */
   186    186     sqlite3_tokenizer *pTokenizer;  /* tokenizer for inserts and queries */
          187  +  char *zContentTbl;              /* content=xxx option, or NULL */
   187    188   
   188    189     /* Precompiled statements used by the implementation. Each of these 
   189    190     ** statements is run and reset within a single virtual table API call. 
   190    191     */
   191    192     sqlite3_stmt *aStmt[27];
   192    193   
   193    194     char *zReadExprlist;
................................................................................
   241    242   ** the xOpen method. Cursors are destroyed using the xClose method.
   242    243   */
   243    244   struct Fts3Cursor {
   244    245     sqlite3_vtab_cursor base;       /* Base class used by SQLite core */
   245    246     i16 eSearch;                    /* Search strategy (see below) */
   246    247     u8 isEof;                       /* True if at End Of Results */
   247    248     u8 isRequireSeek;               /* True if must seek pStmt to %_content row */
          249  +  u8 isNullRow;                   /* True for a row of NULLs */
   248    250     sqlite3_stmt *pStmt;            /* Prepared statement in use by the cursor */
   249    251     Fts3Expr *pExpr;                /* Parsed MATCH query string */
   250    252     int nPhrase;                    /* Number of matchable phrases in query */
   251    253     Fts3DeferredToken *pDeferred;   /* Deferred search tokens, if any */
   252    254     sqlite3_int64 iPrevId;          /* Previous id read from aDoclist */
   253    255     char *pNextId;                  /* Pointer into the body of aDoclist */
   254    256     char *aDoclist;                 /* List of docids for full-text queries */

Changes to ext/fts3/fts3_snippet.c.

  1405   1405             iMinPos = pT->iPos-pT->iOff;
  1406   1406             pTerm = pT;
  1407   1407           }
  1408   1408         }
  1409   1409   
  1410   1410         if( !pTerm ){
  1411   1411           /* All offsets for this column have been gathered. */
  1412         -        break;
         1412  +        rc = SQLITE_DONE;
  1413   1413         }else{
  1414   1414           assert( iCurrent<=iMinPos );
  1415   1415           if( 0==(0xFE&*pTerm->pList) ){
  1416   1416             pTerm->pList = 0;
  1417   1417           }else{
  1418   1418             fts3GetDeltaPosition(&pTerm->pList, &pTerm->iPos);
  1419   1419           }
................................................................................
  1422   1422           }
  1423   1423           if( rc==SQLITE_OK ){
  1424   1424             char aBuffer[64];
  1425   1425             sqlite3_snprintf(sizeof(aBuffer), aBuffer, 
  1426   1426                 "%d %d %d %d ", iCol, pTerm-sCtx.aTerm, iStart, iEnd-iStart
  1427   1427             );
  1428   1428             rc = fts3StringAppend(&res, aBuffer, -1);
  1429         -        }else if( rc==SQLITE_DONE ){
         1429  +        }else if( rc==SQLITE_DONE && pTab->zContentTbl==0 ){
  1430   1430             rc = SQLITE_CORRUPT_VTAB;
  1431   1431           }
  1432   1432         }
  1433   1433       }
  1434   1434       if( rc==SQLITE_DONE ){
  1435   1435         rc = SQLITE_OK;
  1436   1436       }

Changes to ext/fts3/fts3_write.c.

   252    252   /* 0  */  "DELETE FROM %Q.'%q_content' WHERE rowid = ?",
   253    253   /* 1  */  "SELECT NOT EXISTS(SELECT docid FROM %Q.'%q_content' WHERE rowid!=?)",
   254    254   /* 2  */  "DELETE FROM %Q.'%q_content'",
   255    255   /* 3  */  "DELETE FROM %Q.'%q_segments'",
   256    256   /* 4  */  "DELETE FROM %Q.'%q_segdir'",
   257    257   /* 5  */  "DELETE FROM %Q.'%q_docsize'",
   258    258   /* 6  */  "DELETE FROM %Q.'%q_stat'",
   259         -/* 7  */  "SELECT %s FROM %Q.'%q_content' AS x WHERE rowid=?",
          259  +/* 7  */  "SELECT %s WHERE rowid=?",
   260    260   /* 8  */  "SELECT (SELECT max(idx) FROM %Q.'%q_segdir' WHERE level = ?) + 1",
   261    261   /* 9  */  "INSERT INTO %Q.'%q_segments'(blockid, block) VALUES(?, ?)",
   262    262   /* 10 */  "SELECT coalesce((SELECT max(blockid) FROM %Q.'%q_segments') + 1, 1)",
   263    263   /* 11 */  "INSERT INTO %Q.'%q_segdir' VALUES(?,?,?,?,?,?)",
   264    264   
   265    265             /* Return segments in order from oldest to newest.*/ 
   266    266   /* 12 */  "SELECT idx, start_block, leaves_end_block, end_block, root "
................................................................................
   294    294     
   295    295     pStmt = p->aStmt[eStmt];
   296    296     if( !pStmt ){
   297    297       char *zSql;
   298    298       if( eStmt==SQL_CONTENT_INSERT ){
   299    299         zSql = sqlite3_mprintf(azSql[eStmt], p->zDb, p->zName, p->zWriteExprlist);
   300    300       }else if( eStmt==SQL_SELECT_CONTENT_BY_ROWID ){
   301         -      zSql = sqlite3_mprintf(azSql[eStmt], p->zReadExprlist, p->zDb, p->zName);
          301  +      zSql = sqlite3_mprintf(azSql[eStmt], p->zReadExprlist);
   302    302       }else{
   303    303         zSql = sqlite3_mprintf(azSql[eStmt], p->zDb, p->zName);
   304    304       }
   305    305       if( !zSql ){
   306    306         rc = SQLITE_NOMEM;
   307    307       }else{
   308    308         rc = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, NULL);
................................................................................
   775    775   static int fts3InsertData(
   776    776     Fts3Table *p,                   /* Full-text table */
   777    777     sqlite3_value **apVal,          /* Array of values to insert */
   778    778     sqlite3_int64 *piDocid          /* OUT: Docid for row just inserted */
   779    779   ){
   780    780     int rc;                         /* Return code */
   781    781     sqlite3_stmt *pContentInsert;   /* INSERT INTO %_content VALUES(...) */
          782  +
          783  +  if( p->zContentTbl ){
          784  +    sqlite3_value *pRowid = apVal[p->nColumn+3];
          785  +    if( sqlite3_value_type(pRowid)==SQLITE_NULL ){
          786  +      pRowid = apVal[1];
          787  +    }
          788  +    if( sqlite3_value_type(pRowid)!=SQLITE_INTEGER ){
          789  +      return SQLITE_CONSTRAINT;
          790  +    }
          791  +    *piDocid = sqlite3_value_int64(pRowid);
          792  +    return SQLITE_OK;
          793  +  }
   782    794   
   783    795     /* Locate the statement handle used to insert data into the %_content
   784    796     ** table. The SQL for this statement is:
   785    797     **
   786    798     **   INSERT INTO %_content VALUES(?, ?, ?, ...)
   787    799     **
   788    800     ** The statement features N '?' variables, where N is the number of user
................................................................................
   826    838   
   827    839   
   828    840   
   829    841   /*
   830    842   ** Remove all data from the FTS3 table. Clear the hash table containing
   831    843   ** pending terms.
   832    844   */
   833         -static int fts3DeleteAll(Fts3Table *p){
          845  +static int fts3DeleteAll(Fts3Table *p, int bContent){
   834    846     int rc = SQLITE_OK;             /* Return code */
   835    847   
   836    848     /* Discard the contents of the pending-terms hash table. */
   837    849     sqlite3Fts3PendingTermsClear(p);
   838    850   
   839         -  /* Delete everything from the %_content, %_segments and %_segdir tables. */
   840         -  fts3SqlExec(&rc, p, SQL_DELETE_ALL_CONTENT, 0);
          851  +  /* Delete everything from the shadow tables. Except, leave %_content as
          852  +  ** is if bContent is false.  */
          853  +  assert( p->zContentTbl==0 || bContent==0 );
          854  +  if( bContent ) fts3SqlExec(&rc, p, SQL_DELETE_ALL_CONTENT, 0);
   841    855     fts3SqlExec(&rc, p, SQL_DELETE_ALL_SEGMENTS, 0);
   842    856     fts3SqlExec(&rc, p, SQL_DELETE_ALL_SEGDIR, 0);
   843    857     if( p->bHasDocsize ){
   844    858       fts3SqlExec(&rc, p, SQL_DELETE_ALL_DOCSIZE, 0);
   845    859     }
   846    860     if( p->bHasStat ){
   847    861       fts3SqlExec(&rc, p, SQL_DELETE_ALL_STAT, 0);
................................................................................
  2121   2135   ** If successful, *pisEmpty is set to true if the table is empty except for
  2122   2136   ** document pRowid, or false otherwise, and SQLITE_OK is returned. If an
  2123   2137   ** error occurs, an SQLite error code is returned.
  2124   2138   */
  2125   2139   static int fts3IsEmpty(Fts3Table *p, sqlite3_value *pRowid, int *pisEmpty){
  2126   2140     sqlite3_stmt *pStmt;
  2127   2141     int rc;
  2128         -  rc = fts3SqlStmt(p, SQL_IS_EMPTY, &pStmt, &pRowid);
  2129         -  if( rc==SQLITE_OK ){
  2130         -    if( SQLITE_ROW==sqlite3_step(pStmt) ){
  2131         -      *pisEmpty = sqlite3_column_int(pStmt, 0);
         2142  +  if( p->zContentTbl ){
         2143  +    /* If using the content=xxx option, assume the table is never empty */
         2144  +    *pisEmpty = 0;
         2145  +    rc = SQLITE_OK;
         2146  +  }else{
         2147  +    rc = fts3SqlStmt(p, SQL_IS_EMPTY, &pStmt, &pRowid);
         2148  +    if( rc==SQLITE_OK ){
         2149  +      if( SQLITE_ROW==sqlite3_step(pStmt) ){
         2150  +        *pisEmpty = sqlite3_column_int(pStmt, 0);
         2151  +      }
         2152  +      rc = sqlite3_reset(pStmt);
  2132   2153       }
  2133         -    rc = sqlite3_reset(pStmt);
  2134   2154     }
  2135   2155     return rc;
  2136   2156   }
  2137   2157   
  2138   2158   /*
  2139   2159   ** Set *pnMax to the largest segment level in the database for the index
  2140   2160   ** iIndex.
................................................................................
  2783   2803   
  2784   2804   /*
  2785   2805   ** Insert the sizes (in tokens) for each column of the document
  2786   2806   ** with docid equal to p->iPrevDocid.  The sizes are encoded as
  2787   2807   ** a blob of varints.
  2788   2808   */
  2789   2809   static void fts3InsertDocsize(
  2790         -  int *pRC,         /* Result code */
  2791         -  Fts3Table *p,     /* Table into which to insert */
  2792         -  u32 *aSz          /* Sizes of each column */
         2810  +  int *pRC,                       /* Result code */
         2811  +  Fts3Table *p,                   /* Table into which to insert */
         2812  +  u32 *aSz                        /* Sizes of each column, in tokens */
  2793   2813   ){
  2794   2814     char *pBlob;             /* The BLOB encoding of the document size */
  2795   2815     int nBlob;               /* Number of bytes in the BLOB */
  2796   2816     sqlite3_stmt *pStmt;     /* Statement used to insert the encoding */
  2797   2817     int rc;                  /* Result code from subfunctions */
  2798   2818   
  2799   2819     if( *pRC ) return;
................................................................................
  2906   2926       }
  2907   2927     }
  2908   2928     sqlite3Fts3SegmentsClose(p);
  2909   2929     sqlite3Fts3PendingTermsClear(p);
  2910   2930   
  2911   2931     return (rc==SQLITE_OK && bReturnDone && bSeenDone) ? SQLITE_DONE : rc;
  2912   2932   }
         2933  +
         2934  +/*
         2935  +** This function is called when the user executes the following statement:
         2936  +**
         2937  +**     INSERT INTO <tbl>(<tbl>) VALUES('rebuild');
         2938  +**
         2939  +** The entire FTS index is discarded and rebuilt. If the table is one 
         2940  +** created using the content=xxx option, then the new index is based on
         2941  +** the current contents of the xxx table. Otherwise, it is rebuilt based
         2942  +** on the contents of the %_content table.
         2943  +*/
         2944  +static int fts3DoRebuild(Fts3Table *p){
         2945  +  int rc;                         /* Return Code */
         2946  +
         2947  +  rc = fts3DeleteAll(p, 0);
         2948  +  if( rc==SQLITE_OK ){
         2949  +    u32 *aSz = 0;
         2950  +    u32 *aSzIns;
         2951  +    u32 *aSzDel;
         2952  +    sqlite3_stmt *pStmt = 0;
         2953  +    int nEntry = 0;
         2954  +
         2955  +    /* Compose and prepare an SQL statement to loop through the content table */
         2956  +    char *zSql = sqlite3_mprintf("SELECT %s" , p->zReadExprlist);
         2957  +    if( !zSql ){
         2958  +      rc = SQLITE_NOMEM;
         2959  +    }else{
         2960  +      rc = sqlite3_prepare_v2(p->db, zSql, -1, &pStmt, 0);
         2961  +      sqlite3_free(zSql);
         2962  +    }
         2963  +
         2964  +    if( rc==SQLITE_OK ){
         2965  +      int nByte = sizeof(u32) * (p->nColumn+1)*3;
         2966  +      aSz = (u32 *)sqlite3_malloc(nByte);
         2967  +      if( aSz==0 ){
         2968  +        rc = SQLITE_NOMEM;
         2969  +      }else{
         2970  +        memset(aSz, 0, nByte);
         2971  +        aSzIns = &aSz[p->nColumn+1];
         2972  +        aSzDel = &aSzIns[p->nColumn+1];
         2973  +      }
         2974  +    }
         2975  +
         2976  +    while( rc==SQLITE_OK && SQLITE_ROW==sqlite3_step(pStmt) ){
         2977  +      int iCol;
         2978  +      rc = fts3PendingTermsDocid(p, sqlite3_column_int64(pStmt, 0));
         2979  +      aSz[p->nColumn] = 0;
         2980  +      for(iCol=0; rc==SQLITE_OK && iCol<p->nColumn; iCol++){
         2981  +        const char *z = (const char *) sqlite3_column_text(pStmt, iCol+1);
         2982  +        rc = fts3PendingTermsAdd(p, z, iCol, &aSz[iCol]);
         2983  +        aSz[p->nColumn] += sqlite3_column_bytes(pStmt, iCol+1);
         2984  +      }
         2985  +      if( p->bHasDocsize ){
         2986  +        fts3InsertDocsize(&rc, p, aSz);
         2987  +      }
         2988  +      if( rc!=SQLITE_OK ){
         2989  +        sqlite3_finalize(pStmt);
         2990  +        pStmt = 0;
         2991  +      }else{
         2992  +        nEntry++;
         2993  +        for(iCol=0; iCol<=p->nColumn; iCol++){
         2994  +          aSzIns[iCol] += aSz[iCol];
         2995  +        }
         2996  +      }
         2997  +    }
         2998  +    if( p->bHasStat ){
         2999  +      fts3UpdateDocTotals(&rc, p, aSzIns, aSzDel, nEntry);
         3000  +    }
         3001  +    sqlite3_free(aSz);
         3002  +
         3003  +    if( pStmt ){
         3004  +      assert( rc==SQLITE_OK );
         3005  +      rc = sqlite3_finalize(pStmt);
         3006  +    }
         3007  +  }
         3008  +
         3009  +  return rc;
         3010  +}
  2913   3011   
  2914   3012   /*
  2915   3013   ** Handle a 'special' INSERT of the form:
  2916   3014   **
  2917   3015   **   "INSERT INTO tbl(tbl) VALUES(<expr>)"
  2918   3016   **
  2919   3017   ** Argument pVal contains the result of <expr>. Currently the only 
................................................................................
  2924   3022     const char *zVal = (const char *)sqlite3_value_text(pVal);
  2925   3023     int nVal = sqlite3_value_bytes(pVal);
  2926   3024   
  2927   3025     if( !zVal ){
  2928   3026       return SQLITE_NOMEM;
  2929   3027     }else if( nVal==8 && 0==sqlite3_strnicmp(zVal, "optimize", 8) ){
  2930   3028       rc = fts3DoOptimize(p, 0);
         3029  +  }else if( nVal==7 && 0==sqlite3_strnicmp(zVal, "rebuild", 7) ){
         3030  +    rc = fts3DoRebuild(p);
  2931   3031   #ifdef SQLITE_TEST
  2932   3032     }else if( nVal>9 && 0==sqlite3_strnicmp(zVal, "nodesize=", 9) ){
  2933   3033       p->nNodeSize = atoi(&zVal[9]);
  2934   3034       rc = SQLITE_OK;
  2935   3035     }else if( nVal>11 && 0==sqlite3_strnicmp(zVal, "maxpending=", 9) ){
  2936   3036       p->nMaxPendingData = atoi(&zVal[11]);
  2937   3037       rc = SQLITE_OK;
................................................................................
  3095   3195     int isEmpty = 0;
  3096   3196     int rc = fts3IsEmpty(p, pRowid, &isEmpty);
  3097   3197     if( rc==SQLITE_OK ){
  3098   3198       if( isEmpty ){
  3099   3199         /* Deleting this row means the whole table is empty. In this case
  3100   3200         ** delete the contents of all three tables and throw away any
  3101   3201         ** data in the pendingTerms hash table.  */
  3102         -      rc = fts3DeleteAll(p);
         3202  +      rc = fts3DeleteAll(p, 1);
  3103   3203         *pnDoc = *pnDoc - 1;
  3104   3204       }else{
  3105   3205         sqlite3_int64 iRemove = sqlite3_value_int64(pRowid);
  3106   3206         rc = fts3PendingTermsDocid(p, iRemove);
  3107   3207         fts3DeleteTerms(&rc, p, pRowid, aSzDel);
  3108         -      fts3SqlExec(&rc, p, SQL_DELETE_CONTENT, &pRowid);
  3109         -      if( sqlite3_changes(p->db) ) *pnDoc = *pnDoc - 1;
         3208  +      if( p->zContentTbl==0 ){
         3209  +        fts3SqlExec(&rc, p, SQL_DELETE_CONTENT, &pRowid);
         3210  +        if( sqlite3_changes(p->db) ) *pnDoc = *pnDoc - 1;
         3211  +      }else{
         3212  +        *pnDoc = *pnDoc - 1;
         3213  +      }
  3110   3214         if( p->bHasDocsize ){
  3111   3215           fts3SqlExec(&rc, p, SQL_DELETE_DOCSIZE, &pRowid);
  3112   3216         }
  3113   3217       }
  3114   3218     }
  3115   3219   
  3116   3220     return rc;
................................................................................
  3163   3267     **
  3164   3268     ** If the on-conflict mode is REPLACE, this means that the existing row
  3165   3269     ** should be deleted from the database before inserting the new row. Or,
  3166   3270     ** if the on-conflict mode is other than REPLACE, then this method must
  3167   3271     ** detect the conflict and return SQLITE_CONSTRAINT before beginning to
  3168   3272     ** modify the database file.
  3169   3273     */
  3170         -  if( nArg>1 ){
         3274  +  if( nArg>1 && p->zContentTbl==0 ){
  3171   3275       /* Find the value object that holds the new rowid value. */
  3172   3276       sqlite3_value *pNewRowid = apVal[3+p->nColumn];
  3173   3277       if( sqlite3_value_type(pNewRowid)==SQLITE_NULL ){
  3174   3278         pNewRowid = apVal[1];
  3175   3279       }
  3176   3280   
  3177   3281       if( sqlite3_value_type(pNewRowid)!=SQLITE_NULL && ( 
................................................................................
  3215   3319       iRemove = sqlite3_value_int64(apVal[0]);
  3216   3320     }
  3217   3321     
  3218   3322     /* If this is an INSERT or UPDATE operation, insert the new record. */
  3219   3323     if( nArg>1 && rc==SQLITE_OK ){
  3220   3324       if( bInsertDone==0 ){
  3221   3325         rc = fts3InsertData(p, apVal, pRowid);
  3222         -      if( rc==SQLITE_CONSTRAINT ) rc = SQLITE_CORRUPT_VTAB;
         3326  +      if( rc==SQLITE_CONSTRAINT && p->zContentTbl==0 ){
         3327  +        rc = SQLITE_CORRUPT_VTAB;
         3328  +      }
  3223   3329       }
  3224   3330       if( rc==SQLITE_OK && (!isRemove || *pRowid!=iRemove) ){
  3225   3331         rc = fts3PendingTermsDocid(p, *pRowid);
  3226   3332       }
  3227   3333       if( rc==SQLITE_OK ){
  3228   3334         rc = fts3InsertTerms(p, apVal, aSzIns);
  3229   3335       }

Added test/fts4content.test.

            1  +# 2011 October 03
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#*************************************************************************
           11  +# This file implements regression tests for SQLite library.  The
           12  +# focus of this script is testing the content=xxx FTS4 option.
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +set ::testprefix fts4content
           18  +
           19  +# If SQLITE_ENABLE_FTS3 is defined, omit this file.
           20  +ifcapable !fts3 {
           21  +  finish_test
           22  +  return
           23  +}
           24  +
           25  +do_execsql_test 1.1 {
           26  +  CREATE TABLE t1(a, b, c);
           27  +  INSERT INTO t1 VALUES('w x', 'x y', 'y z');
           28  +  CREATE VIRTUAL TABLE ft1 USING fts4(content=t1);
           29  +}
           30  +
           31  +do_execsql_test 1.2 {
           32  +  PRAGMA table_info(ft1);
           33  +} {
           34  +  0 a {} 0 {} 0 
           35  +  1 b {} 0 {} 0 
           36  +  2 c {} 0 {} 0
           37  +}
           38  +
           39  +do_execsql_test 1.3 { SELECT *, rowid FROM ft1 } {{w x} {x y} {y z} 1}
           40  +do_execsql_test 1.4 { SELECT a, c FROM ft1 WHERE rowid=1 } {{w x} {y z}}
           41  +
           42  +do_execsql_test 1.5 { INSERT INTO ft1(ft1) VALUES('rebuild') } {}
           43  +do_execsql_test 1.6 { SELECT rowid FROM ft1 WHERE ft1 MATCH 'x' } {1}
           44  +do_execsql_test 1.7 { SELECT rowid FROM ft1 WHERE ft1 MATCH 'a' } {}
           45  +
           46  +#-------------------------------------------------------------------------
           47  +# The following block of tests - 2.* - test that a content=xxx FTS table
           48  +# can be queried. Also tested are cases where rows identified in the FTS
           49  +# are missing from the content table, and cases where the index is 
           50  +# inconsistent with the content table.
           51  +# 
           52  +do_execsql_test 2.0 {
           53  +  CREATE TABLE t2(x);
           54  +  INSERT INTO t2 VALUES('O S W W F U C R Q I C N P Z Y Y E Y Y E');  -- 1
           55  +  INSERT INTO t2 VALUES('Y X U V L B E H Y J C Y A I A P V F V K');  -- 2
           56  +  INSERT INTO t2 VALUES('P W I N J H I I N I F B K D U Q B Z S F');  -- 3
           57  +  INSERT INTO t2 VALUES('N R O R H J R H G M D I U U B O M P A U');  -- 4
           58  +  INSERT INTO t2 VALUES('Y O V O G T P N G T N F I V B U M J M G');  -- 5
           59  +  INSERT INTO t2 VALUES('J O B N K N E C H Z R K J O U G M K L S');  -- 6
           60  +  INSERT INTO t2 VALUES('S Z S R I Q U A P W R X H K C Z U L S P');  -- 7
           61  +  INSERT INTO t2 VALUES('J C H N R C K R V N M O F Z M Z A I H W');  -- 8
           62  +  INSERT INTO t2 VALUES('O Y G I S J U U W O D Z F J K N R P R L');  -- 9
           63  +  INSERT INTO t2 VALUES('B G L K U R U P V X Z I H V R W C Q A S');  -- 10
           64  +  INSERT INTO t2 VALUES('T F T J F F Y V F W N X K Q A Y L X W G');  -- 11
           65  +  INSERT INTO t2 VALUES('C J U H B Q X L C M M Y E G V F W V Z C');  -- 12
           66  +  INSERT INTO t2 VALUES('B W L T F S G X D P H N G M R I O A X I');  -- 13
           67  +  INSERT INTO t2 VALUES('N G Y O K Q K Z N M H U J E D H U W R K');  -- 14
           68  +  INSERT INTO t2 VALUES('U D T R U Y F J D S J X E H Q G V A S Z');  -- 15
           69  +  INSERT INTO t2 VALUES('M I W P J S H R J D Q I C G P C T P H R');  -- 16
           70  +  INSERT INTO t2 VALUES('J M N I S L X Q C A B F C B Y D H V R J');  -- 17
           71  +  INSERT INTO t2 VALUES('F V Z W J Q L P X Y E W B U Q N H X K T');  -- 18
           72  +  INSERT INTO t2 VALUES('R F S R Y O F Q E I E G H C B H R X Y N');  -- 19
           73  +  INSERT INTO t2 VALUES('U Q Q Q T E P D M F X P J G H X C Q D L');  -- 20
           74  +}
           75  +
           76  +do_execsql_test 2.1 {
           77  +  CREATE VIRTUAL TABLE ft2 USING fts4(content=t2);
           78  +  INSERT INTO ft2(ft2) VALUES('rebuild');
           79  +
           80  +  -- Modify the backing table a bit: Row 17 is missing and the contents 
           81  +  -- of row 20 do not match the FTS index contents. 
           82  +  DELETE FROM t2 WHERE rowid = 17;
           83  +  UPDATE t2 SET x = 'a b c d e f g h i j' WHERE rowid = 20;
           84  +}
           85  +
           86  +foreach {tn match rowidlist} {
           87  +  1   {S}        {1 3 6 7 9 10 13 15 16 17 19}
           88  +  2   {"S R"}    {7 19}
           89  +  3   {"N K N"}  {6}
           90  +  4   {"Q Q"}    {20}
           91  +  5   {"B Y D"}  {17}
           92  +} {
           93  +  do_execsql_test 2.2.1.$tn {
           94  +    SELECT rowid FROM ft2 WHERE ft2 MATCH $match
           95  +  } $rowidlist
           96  +
           97  +  do_execsql_test 2.2.2.$tn {
           98  +    SELECT docid FROM ft2 WHERE ft2 MATCH $match
           99  +  } $rowidlist
          100  +}
          101  +
          102  +foreach {tn match result} {
          103  +  1   {"N K N"}  {{J O B N K N E C H Z R K J O U G M K L S}}
          104  +  2   {"Q Q"}    {{a b c d e f g h i j}}
          105  +  3   {"B Y D"}  {{}}
          106  +} {
          107  +  do_execsql_test 2.3.$tn {
          108  +    SELECT * FROM ft2 WHERE ft2 MATCH $match
          109  +  } $result
          110  +}
          111  +
          112  +foreach {tn match result} {
          113  +  1   {"N K N"}  {{..O B [N] [K] [N] E..}}
          114  +  2   {"B Y D"}  {{}}
          115  +  3   {"Q Q"}    {{a [b] [c] [d] e f..}}
          116  +} {
          117  +  do_execsql_test 2.4.$tn {
          118  +    SELECT snippet(ft2, '[', ']', '..', -1, 6) FROM ft2 WHERE ft2 MATCH $match
          119  +  } $result
          120  +}
          121  +
          122  +foreach {tn match result} {
          123  +  1   {"N K N"}  {{0 0 6 1 0 1 8 1 0 2 10 1}}
          124  +  2   {"B Y D"}  {{}}
          125  +  3   {"Q Q"}    {{0 0 2 1 0 0 4 1 0 1 4 1 0 1 6 1}}
          126  +  4   {"Q D L"}  {{}}
          127  +} {
          128  +  do_execsql_test 2.5.$tn {
          129  +    SELECT offsets(ft2) FROM ft2 WHERE ft2 MATCH $match
          130  +  } $result
          131  +}
          132  +
          133  +#-------------------------------------------------------------------------
          134  +# The following block of tests - 3.* - test that the FTS index can be
          135  +# modified by writing to the table. But that this has no effect on the 
          136  +# content table.
          137  +# 
          138  +
          139  +do_execsql_test 3.1 {
          140  +  CREATE TABLE t3(x, y);
          141  +  CREATE VIRTUAL TABLE ft3 USING fts4(content=t3);
          142  +}
          143  +
          144  +do_catchsql_test 3.1.1 {
          145  +  INSERT INTO ft3 VALUES('a b c', 'd e f');
          146  +} {1 {constraint failed}}
          147  +do_execsql_test 3.1.2 {
          148  +  INSERT INTO ft3(docid, x, y) VALUES(21, 'a b c', 'd e f');
          149  +  SELECT rowid FROM ft3 WHERE ft3 MATCH '"a b c"';
          150  +} {21}
          151  +do_execsql_test 3.1.3 { SELECT * FROM t3 } {}
          152  +
          153  +# This DELETE does not work, since there is no row in [t3] to base the
          154  +# DELETE on. So the SELECT on [ft3] still returns rowid 21.
          155  +do_execsql_test 3.1.4 { 
          156  +  DELETE FROM ft3;
          157  +  SELECT rowid FROM ft3 WHERE ft3 MATCH '"a b c"';
          158  +} {21}
          159  +
          160  +# If the row is added to [t3] before the DELETE on [ft3], it works.
          161  +do_execsql_test 3.1.5 {
          162  +  INSERT INTO t3(rowid, x, y) VALUES(21, 'a b c', 'd e f');
          163  +  DELETE FROM ft3;
          164  +  SELECT rowid FROM ft3 WHERE ft3 MATCH '"a b c"';
          165  +} {}
          166  +do_execsql_test 3.1.6 { SELECT rowid FROM t3 } {21}
          167  +
          168  +do_execsql_test 3.2.1 {
          169  +  INSERT INTO ft3(rowid, x, y) VALUES(0, 'R T M S M', 'A F O K H');
          170  +  INSERT INTO ft3(rowid, x, y) VALUES(1, 'C Z J O X', 'U S Q D K');
          171  +  INSERT INTO ft3(rowid, x, y) VALUES(2, 'N G H P O', 'N O P O C');
          172  +  INSERT INTO ft3(rowid, x, y) VALUES(3, 'V H S D R', 'K N G E C');
          173  +  INSERT INTO ft3(rowid, x, y) VALUES(4, 'J T R V U', 'U X S L C');
          174  +  INSERT INTO ft3(rowid, x, y) VALUES(5, 'N A Y N G', 'X D G P Y');
          175  +  INSERT INTO ft3(rowid, x, y) VALUES(6, 'I Q I S P', 'D R O Q B');
          176  +  INSERT INTO ft3(rowid, x, y) VALUES(7, 'T K T Z J', 'B W D G O');
          177  +  INSERT INTO ft3(rowid, x, y) VALUES(8, 'Y K F X T', 'D F G V G');
          178  +  INSERT INTO ft3(rowid, x, y) VALUES(9, 'E L E T L', 'P W N F Z');
          179  +  INSERT INTO ft3(rowid, x, y) VALUES(10, 'O G J G X', 'G J F E P');
          180  +  INSERT INTO ft3(rowid, x, y) VALUES(11, 'O L N N Z', 'K E Z F D');
          181  +  INSERT INTO ft3(rowid, x, y) VALUES(12, 'R Z M R J', 'X G I M Z');
          182  +  INSERT INTO ft3(rowid, x, y) VALUES(13, 'L X N N X', 'R R N S T');
          183  +  INSERT INTO ft3(rowid, x, y) VALUES(14, 'F L B J H', 'K W F L C');
          184  +  INSERT INTO ft3(rowid, x, y) VALUES(15, 'P E B M V', 'E A A B U');
          185  +  INSERT INTO ft3(rowid, x, y) VALUES(16, 'V E C F P', 'L U T V K');
          186  +  INSERT INTO ft3(rowid, x, y) VALUES(17, 'T N O Z N', 'T P Q X N');
          187  +  INSERT INTO ft3(rowid, x, y) VALUES(18, 'V W U W R', 'H O A A V');
          188  +  INSERT INTO ft3(rowid, x, y) VALUES(19, 'A H N L F', 'I G H B O');
          189  +}
          190  +
          191  +foreach {tn match rowidlist} {
          192  +  1   "N A"    {5 19}
          193  +  2   "x:O"    {1 2 10 11 17}
          194  +  3   "y:O"    {0 2 6 7 18 19}
          195  +} {
          196  +  set res [list]
          197  +  foreach rowid $rowidlist { lappend res $rowid {} {} }
          198  +
          199  +  do_execsql_test 3.2.2.$tn {
          200  +    SELECT rowid, * FROM ft3 WHERE ft3 MATCH $match
          201  +  } $res
          202  +  do_execsql_test 3.2.3.$tn {
          203  +    SELECT docid, * FROM ft3 WHERE ft3 MATCH $match
          204  +  } $res
          205  +}
          206  +
          207  +do_execsql_test 3.3.1 {
          208  +  INSERT INTO t3(rowid, x, y) VALUES(0, 'R T M S M', 'A F O K H');
          209  +  INSERT INTO t3(rowid, x, y) VALUES(1, 'C Z J O X', 'U S Q D K');
          210  +  INSERT INTO t3(rowid, x, y) VALUES(2, 'N G H P O', 'N O P O C');
          211  +  INSERT INTO t3(rowid, x, y) VALUES(3, 'V H S D R', 'K N G E C');
          212  +  INSERT INTO t3(rowid, x, y) VALUES(4, 'J T R V U', 'U X S L C');
          213  +  INSERT INTO t3(rowid, x, y) VALUES(5, 'N A Y N G', 'X D G P Y');
          214  +  UPDATE ft3 SET x = y, y = x;
          215  +  DELETE FROM t3;
          216  +}
          217  +
          218  +foreach {tn match rowidlist} {
          219  +  1   "N A"    {5 19}
          220  +  2   "x:O"    {0 2 10 11 17}
          221  +  3   "y:O"    {1 2 6 7 18 19}
          222  +} {
          223  +  set res [list]
          224  +  foreach rowid $rowidlist { lappend res $rowid {} {} }
          225  +
          226  +  do_execsql_test 3.3.2.$tn {
          227  +    SELECT rowid, * FROM ft3 WHERE ft3 MATCH $match
          228  +  } $res
          229  +  do_execsql_test 3.3.3.$tn {
          230  +    SELECT docid, * FROM ft3 WHERE ft3 MATCH $match
          231  +  } $res
          232  +}
          233  +
          234  +do_execsql_test 3.3.1 {
          235  +  INSERT INTO t3(rowid, x, y) VALUES(15, 'P E B M V', 'E A A B U');
          236  +  INSERT INTO t3(rowid, x, y) VALUES(16, 'V E C F P', 'L U T V K');
          237  +  INSERT INTO t3(rowid, x, y) VALUES(17, 'T N O Z N', 'T P Q X N');
          238  +  INSERT INTO t3(rowid, x, y) VALUES(18, 'V W U W R', 'H O A A V');
          239  +  INSERT INTO t3(rowid, x, y) VALUES(19, 'A H N L F', 'I G H B O');
          240  +  DELETE FROM ft3;
          241  +}
          242  +
          243  +foreach {tn match rowidlist} {
          244  +  1   "N A"    {5}
          245  +  2   "x:O"    {0 2 10 11}
          246  +  3   "y:O"    {1 2 6 7}
          247  +} {
          248  +  set res [list]
          249  +  foreach rowid $rowidlist { lappend res $rowid {} {} }
          250  +
          251  +  do_execsql_test 3.3.2.$tn {
          252  +    SELECT rowid, * FROM ft3 WHERE ft3 MATCH $match
          253  +  } $res
          254  +  do_execsql_test 3.3.3.$tn {
          255  +    SELECT docid, * FROM ft3 WHERE ft3 MATCH $match
          256  +  } $res
          257  +}
          258  +
          259  +
          260  +#-------------------------------------------------------------------------
          261  +# Test cases 4.* test the 'rebuild' command. On content=xxx and regular
          262  +# FTS tables.
          263  +# 
          264  +do_execsql_test 4.0 {
          265  +  CREATE TABLE t4(x);
          266  +  CREATE VIRTUAL TABLE ft4 USING fts4(content=t4);
          267  +  CREATE VIRTUAL TABLE ft4x USING fts4(x);
          268  +}
          269  +
          270  +do_execsql_test 4.1.1 {
          271  +  INSERT INTO ft4x(ft4x) VALUES('rebuild');
          272  +  INSERT INTO ft4(ft4) VALUES('rebuild');
          273  +} {}
          274  +do_execsql_test 4.1.2 {
          275  +  SELECT id, quote(value) FROM ft4_stat
          276  +} {0 X'000000'}
          277  +do_execsql_test 4.1.3 {
          278  +  SELECT id, quote(value) FROM ft4x_stat
          279  +} {0 X'000000'}
          280  +
          281  +do_execsql_test 4.2.1 {
          282  +  INSERT INTO ft4x VALUES('M G M F T');
          283  +  INSERT INTO ft4x VALUES('Z Q C A U');
          284  +  INSERT INTO ft4x VALUES('N L L V');
          285  +  INSERT INTO ft4x VALUES('T F D X D');
          286  +  INSERT INTO ft4x VALUES('Z H I S D');
          287  +
          288  +  SELECT id, quote(value) FROM ft4x_stat
          289  +} {0 X'05182B'}
          290  +
          291  +do_execsql_test 4.2.2 {
          292  +  INSERT INTO ft4(rowid, x) SELECT rowid, * FROM ft4x;
          293  +  SELECT id, quote(value) FROM ft4_stat
          294  +} {0 X'05182B'}
          295  +
          296  +do_execsql_test 4.2.3 {
          297  +  SELECT docid, quote(size) FROM ft4_docsize
          298  +} {1 X'05' 2 X'05' 3 X'04' 4 X'05' 5 X'05'}
          299  +
          300  +do_execsql_test 4.2.4 {
          301  +  INSERT INTO ft4x(ft4x) VALUES('rebuild');
          302  +  SELECT id, quote(value) FROM ft4x_stat;
          303  +  SELECT docid, quote(size) FROM ft4x_docsize
          304  +} {0 X'05182B' 1 X'05' 2 X'05' 3 X'04' 4 X'05' 5 X'05'}
          305  +
          306  +do_execsql_test 4.2.5 {
          307  +  INSERT INTO ft4(ft4) VALUES('rebuild');
          308  +  SELECT id, quote(value) FROM ft4_stat;
          309  +  SELECT docid, quote(size) FROM ft4_docsize
          310  +} {0 X'000000'}
          311  +
          312  +do_execsql_test 4.2.6 {
          313  +  INSERT INTO t4(rowid, x) SELECT rowid, x FROM ft4x;
          314  +  INSERT INTO ft4(ft4) VALUES('rebuild');
          315  +  SELECT id, quote(value) FROM ft4_stat;
          316  +  SELECT docid, quote(size) FROM ft4_docsize
          317  +} {0 X'05182B' 1 X'05' 2 X'05' 3 X'04' 4 X'05' 5 X'05'}
          318  +
          319  +
          320  +#-------------------------------------------------------------------------
          321  +# Test cases 5.* test that the following commands do not create/move or
          322  +# delete a %_content table when used with a content=xxx FTS table.
          323  +# 
          324  +do_execsql_test 5.1.1 {
          325  +  CREATE TABLE t5(a, b, c, d);
          326  +  CREATE VIRTUAL TABLE ft5 USING fts4(content=t5);
          327  +  SELECT name FROM sqlite_master WHERE name LIKE '%t5%';
          328  +} {
          329  +  t5 ft5 ft5_segments ft5_segdir 
          330  +  sqlite_autoindex_ft5_segdir_1 ft5_docsize ft5_stat
          331  +}
          332  +do_execsql_test 5.1.2 {
          333  +  ALTER TABLE ft5 RENAME TO ft6;
          334  +  SELECT name FROM sqlite_master WHERE name LIKE '%t5%';
          335  +} {
          336  +  t5
          337  +}
          338  +do_execsql_test 5.1.3 {
          339  +  SELECT name FROM sqlite_master WHERE name LIKE '%t6%';
          340  +} {
          341  +  ft6 ft6_segments ft6_segdir 
          342  +  sqlite_autoindex_ft6_segdir_1 ft6_docsize ft6_stat
          343  +}
          344  +do_execsql_test 5.1.4 {
          345  +  INSERT INTO t5 VALUES('a', 'b', 'c', 'd');
          346  +  INSERT INTO ft6(ft6) VALUES('rebuild');
          347  +  SELECT rowid FROM ft6 WHERE ft6 MATCH 'b';
          348  +} {1}
          349  +do_execsql_test 5.1.5 {
          350  +  DROP TABLE ft6;
          351  +  SELECT * FROM t5;
          352  +} {a b c d}
          353  +do_execsql_test 5.1.6 {
          354  +  SELECT name FROM sqlite_master WHERE name LIKE '%t6%';
          355  +} {
          356  +}
          357  +do_execsql_test 5.1.7 {
          358  +  CREATE VIRTUAL TABLE ft5 USING fts4(content=t5);
          359  +  CREATE TABLE t5_content(a, b);
          360  +  DROP TABLE ft5;
          361  +  SELECT name FROM sqlite_master WHERE name LIKE '%t5%';
          362  +} {
          363  +  t5 t5_content
          364  +}
          365  +
          366  +finish_test

Changes to test/permutations.test.

   176    176     fts3aa.test fts3ab.test fts3ac.test fts3ad.test fts3ae.test
   177    177     fts3af.test fts3ag.test fts3ah.test fts3ai.test fts3aj.test
   178    178     fts3ak.test fts3al.test fts3am.test fts3an.test fts3ao.test
   179    179     fts3atoken.test fts3b.test fts3c.test fts3cov.test fts3d.test
   180    180     fts3defer.test fts3defer2.test fts3e.test fts3expr.test fts3expr2.test 
   181    181     fts3near.test fts3query.test fts3shared.test fts3snippet.test 
   182    182     fts3sort.test
   183         -
   184    183     fts3fault.test fts3malloc.test fts3matchinfo.test
   185         -
   186    184     fts3aux1.test fts3comp1.test fts3auto.test
          185  +  fts4aa.test fts4content.test
   187    186   }
   188    187   
   189    188   
   190    189   lappend ::testsuitelist xxx
   191    190   #-------------------------------------------------------------------------
   192    191   # Define the coverage related test suites:
   193    192   #