/ Check-in [b3b505a4]
Login

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

Overview
Comment:Experimental code to prevent FTS indexes from growing indefinitely as the table is updated.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | fts4-experimental
Files: files | file ages | folders
SHA1: b3b505a4dd0c679437a4272109f1188175088cd1
User & Date: dan 2014-05-12 20:04:48
Context
2014-05-13
20:11
Fix a problem preventing delete markers from ever being removed from the FTS index. check-in: 7f47ae5c user: dan tags: fts4-experimental
2014-05-12
20:04
Experimental code to prevent FTS indexes from growing indefinitely as the table is updated. check-in: b3b505a4 user: dan tags: fts4-experimental
2014-05-10
17:28
When using Visual Studio 2013, add the appropriate MaxPlatformVersion attribute to the VSIX SDK manifest. check-in: 0a4f5967 user: mistachkin tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/fts3/fts3Int.h.

   212    212     char *zLanguageid;              /* languageid=xxx option, or NULL */
   213    213     u8 bAutoincrmerge;              /* True if automerge=1 */
   214    214     u32 nLeafAdd;                   /* Number of leaf blocks added this trans */
   215    215   
   216    216     /* Precompiled statements used by the implementation. Each of these 
   217    217     ** statements is run and reset within a single virtual table API call. 
   218    218     */
   219         -  sqlite3_stmt *aStmt[37];
          219  +  sqlite3_stmt *aStmt[40];
   220    220   
   221    221     char *zReadExprlist;
   222    222     char *zWriteExprlist;
   223    223   
   224    224     int nNodeSize;                  /* Soft limit for node size */
   225    225     u8 bFts4;                       /* True for FTS4, false for FTS3 */
   226    226     u8 bHasStat;                    /* True if %_stat table exists (2==unknown) */

Changes to ext/fts3/fts3_write.c.

   189    189     char *zTerm;                    /* Pointer to previous term buffer */
   190    190     int nTerm;                      /* Number of bytes in zTerm */
   191    191     int nMalloc;                    /* Size of malloc'd buffer at zMalloc */
   192    192     char *zMalloc;                  /* Malloc'd space (possibly) used for zTerm */
   193    193     int nSize;                      /* Size of allocation at aData */
   194    194     int nData;                      /* Bytes of data in aData */
   195    195     char *aData;                    /* Pointer to block from malloc() */
          196  +  i64 nLeafData;                  /* Number of bytes of leaf data written */
   196    197   };
   197    198   
   198    199   /*
   199    200   ** Type SegmentNode is used by the following three functions to create
   200    201   ** the interior part of the segment b+-tree structures (everything except
   201    202   ** the leaf nodes). These functions and type are only ever used by code
   202    203   ** within the fts3SegWriterXXX() family of functions described above.
................................................................................
   263    264   #define SQL_DELETE_SEGDIR_ENTRY       30
   264    265   #define SQL_SHIFT_SEGDIR_ENTRY        31
   265    266   #define SQL_SELECT_SEGDIR             32
   266    267   #define SQL_CHOMP_SEGDIR              33
   267    268   #define SQL_SEGMENT_IS_APPENDABLE     34
   268    269   #define SQL_SELECT_INDEXES            35
   269    270   #define SQL_SELECT_MXLEVEL            36
          271  +
          272  +#define SQL_SELECT_LEVEL_RANGE2       37
          273  +#define SQL_UPDATE_LEVEL_IDX          38
          274  +#define SQL_UPDATE_LEVEL              39
   270    275   
   271    276   /*
   272    277   ** This function is used to obtain an SQLite prepared statement handle
   273    278   ** for the statement identified by the second argument. If successful,
   274    279   ** *pp is set to the requested statement handle and SQLITE_OK returned.
   275    280   ** Otherwise, an SQLite error code is returned and *pp is set to 0.
   276    281   **
................................................................................
   365    370   
   366    371   /* SQL_SELECT_INDEXES
   367    372   **   Return the list of valid segment indexes for absolute level ?  */
   368    373   /* 35 */  "SELECT idx FROM %Q.'%q_segdir' WHERE level=? ORDER BY 1 ASC",
   369    374   
   370    375   /* SQL_SELECT_MXLEVEL
   371    376   **   Return the largest relative level in the FTS index or indexes.  */
   372         -/* 36 */  "SELECT max( level %% 1024 ) FROM %Q.'%q_segdir'"
          377  +/* 36 */  "SELECT max( level %% 1024 ) FROM %Q.'%q_segdir'",
          378  +
          379  +          /* Return segments in order from oldest to newest.*/ 
          380  +/* 37 */  "SELECT level, idx, end_block "
          381  +            "FROM %Q.'%q_segdir' WHERE level BETWEEN ? AND ?"
          382  +            "ORDER BY level DESC, idx ASC",
          383  +
          384  +          /* Update statements used while promoting segments */
          385  +/* 38 */  "UPDATE %Q.'%q_segdir' SET level=-1,idx=? WHERE level=? AND idx=?",
          386  +/* 39 */  "UPDATE %Q.'%q_segdir' SET level=? WHERE level=-1"
          387  +
   373    388     };
   374    389     int rc = SQLITE_OK;
   375    390     sqlite3_stmt *pStmt;
   376    391   
   377    392     assert( SizeofArray(azSql)==SizeofArray(p->aStmt) );
   378    393     assert( eStmt<SizeofArray(azSql) && eStmt>=0 );
   379    394     
................................................................................
  1906   1921   static int fts3WriteSegdir(
  1907   1922     Fts3Table *p,                   /* Virtual table handle */
  1908   1923     sqlite3_int64 iLevel,           /* Value for "level" field (absolute level) */
  1909   1924     int iIdx,                       /* Value for "idx" field */
  1910   1925     sqlite3_int64 iStartBlock,      /* Value for "start_block" field */
  1911   1926     sqlite3_int64 iLeafEndBlock,    /* Value for "leaves_end_block" field */
  1912   1927     sqlite3_int64 iEndBlock,        /* Value for "end_block" field */
         1928  +  sqlite3_int64 nLeafData,        /* Bytes of leaf data in segment */
  1913   1929     char *zRoot,                    /* Blob value for "root" field */
  1914   1930     int nRoot                       /* Number of bytes in buffer zRoot */
  1915   1931   ){
  1916   1932     sqlite3_stmt *pStmt;
  1917   1933     int rc = fts3SqlStmt(p, SQL_INSERT_SEGDIR, &pStmt, 0);
  1918   1934     if( rc==SQLITE_OK ){
  1919   1935       sqlite3_bind_int64(pStmt, 1, iLevel);
  1920   1936       sqlite3_bind_int(pStmt, 2, iIdx);
  1921   1937       sqlite3_bind_int64(pStmt, 3, iStartBlock);
  1922   1938       sqlite3_bind_int64(pStmt, 4, iLeafEndBlock);
  1923         -    sqlite3_bind_int64(pStmt, 5, iEndBlock);
         1939  +    if( nLeafData==0 ){
         1940  +      sqlite3_bind_int64(pStmt, 5, iEndBlock);
         1941  +    }else{
         1942  +      char *zEnd = sqlite3_mprintf("%lld %lld", iEndBlock, nLeafData);
         1943  +      if( !zEnd ) return SQLITE_NOMEM;
         1944  +      sqlite3_bind_text(pStmt, 5, zEnd, -1, sqlite3_free);
         1945  +    }
  1924   1946       sqlite3_bind_blob(pStmt, 6, zRoot, nRoot, SQLITE_STATIC);
  1925   1947       sqlite3_step(pStmt);
  1926   1948       rc = sqlite3_reset(pStmt);
  1927   1949     }
  1928   1950     return rc;
  1929   1951   }
  1930   1952   
................................................................................
  2241   2263       nSuffix = nTerm;
  2242   2264       nReq = 1 +                              /* varint containing prefix size */
  2243   2265         sqlite3Fts3VarintLen(nTerm) +         /* varint containing suffix size */
  2244   2266         nTerm +                               /* Term suffix */
  2245   2267         sqlite3Fts3VarintLen(nDoclist) +      /* Size of doclist */
  2246   2268         nDoclist;                             /* Doclist data */
  2247   2269     }
         2270  +
         2271  +  /* Increase the total number of bytes written to account for the new entry. */
         2272  +  pWriter->nLeafData += nReq;
  2248   2273   
  2249   2274     /* If the buffer currently allocated is too small for this entry, realloc
  2250   2275     ** the buffer to make it large enough.
  2251   2276     */
  2252   2277     if( nReq>pWriter->nSize ){
  2253   2278       char *aNew = sqlite3_realloc(pWriter->aData, nReq);
  2254   2279       if( !aNew ) return SQLITE_NOMEM;
................................................................................
  2313   2338       iLastLeaf = pWriter->iFree;
  2314   2339       rc = fts3WriteSegment(p, pWriter->iFree++, pWriter->aData, pWriter->nData);
  2315   2340       if( rc==SQLITE_OK ){
  2316   2341         rc = fts3NodeWrite(p, pWriter->pTree, 1,
  2317   2342             pWriter->iFirst, pWriter->iFree, &iLast, &zRoot, &nRoot);
  2318   2343       }
  2319   2344       if( rc==SQLITE_OK ){
  2320         -      rc = fts3WriteSegdir(
  2321         -          p, iLevel, iIdx, pWriter->iFirst, iLastLeaf, iLast, zRoot, nRoot);
         2345  +      rc = fts3WriteSegdir(p, iLevel, iIdx, 
         2346  +          pWriter->iFirst, iLastLeaf, iLast, pWriter->nLeafData, zRoot, nRoot);
  2322   2347       }
  2323   2348     }else{
  2324   2349       /* The entire tree fits on the root node. Write it to the segdir table. */
  2325         -    rc = fts3WriteSegdir(
  2326         -        p, iLevel, iIdx, 0, 0, 0, pWriter->aData, pWriter->nData);
         2350  +    rc = fts3WriteSegdir(p, iLevel, iIdx, 
         2351  +        0, 0, 0, pWriter->nLeafData, pWriter->aData, pWriter->nData);
  2327   2352     }
  2328   2353     p->nLeafAdd++;
  2329   2354     return rc;
  2330   2355   }
  2331   2356   
  2332   2357   /*
  2333   2358   ** Release all memory held by the SegmentWriter object passed as the 
................................................................................
  2937   2962       sqlite3_free(pCsr->aBuffer);
  2938   2963   
  2939   2964       pCsr->nSegment = 0;
  2940   2965       pCsr->apSegment = 0;
  2941   2966       pCsr->aBuffer = 0;
  2942   2967     }
  2943   2968   }
         2969  +
         2970  +/*
         2971  +** Decode the "end_block" field, selected by column iCol of the SELECT 
         2972  +** statement passed as the first argument. 
         2973  +*/
         2974  +static void fts3ReadEndBlockField(
         2975  +  sqlite3_stmt *pStmt, 
         2976  +  int iCol, 
         2977  +  i64 *piEndBlock, 
         2978  +  i64 *pnByte
         2979  +){
         2980  +  const unsigned char *zText = sqlite3_column_text(pStmt, iCol);
         2981  +  if( zText ){
         2982  +    int i;
         2983  +    i64 iVal = 0;
         2984  +    for(i=0; zText[i]>='0' && zText[i]<='9'; i++){
         2985  +      iVal = iVal*10 + (zText[i] - '0');
         2986  +    }
         2987  +    *piEndBlock = iVal;
         2988  +    while( zText[i]==' ' ) i++;
         2989  +    iVal = 0;
         2990  +    for(/* no-op */; zText[i]>='0' && zText[i]<='9'; i++){
         2991  +      iVal = iVal*10 + (zText[i] - '0');
         2992  +    }
         2993  +    *pnByte = iVal;
         2994  +  }
         2995  +}
         2996  +
         2997  +
         2998  +/*
         2999  +** A segment of size nByte bytes has just been written to absolute level
         3000  +** iAbsLevel. Promote any segments that should be promoted as a result.
         3001  +*/
         3002  +static int fts3PromoteSegments(
         3003  +  Fts3Table *p,                   /* FTS table handle */
         3004  +  int iAbsLevel,                  /* Absolute level just updated */
         3005  +  sqlite3_int64 nByte             /* Size of new segment at iAbsLevel */
         3006  +){
         3007  +  int rc = SQLITE_OK;
         3008  +  sqlite3_stmt *pRange;
         3009  +
         3010  +  rc = fts3SqlStmt(p, SQL_SELECT_LEVEL_RANGE2, &pRange, 0);
         3011  +
         3012  +  if( rc==SQLITE_OK ){
         3013  +    int bOk = 1;
         3014  +    int iLast = (iAbsLevel/FTS3_SEGDIR_MAXLEVEL + 1) * FTS3_SEGDIR_MAXLEVEL - 1;
         3015  +
         3016  +    sqlite3_bind_int(pRange, 1, iAbsLevel+1);
         3017  +    sqlite3_bind_int(pRange, 2, iLast);
         3018  +    while( SQLITE_ROW==sqlite3_step(pRange) ){
         3019  +      i64 nSize, dummy;
         3020  +      fts3ReadEndBlockField(pRange, 2, &dummy, &nSize);
         3021  +      if( nSize>nByte ){
         3022  +        bOk = 0;
         3023  +        break;
         3024  +      }
         3025  +    }
         3026  +    rc = sqlite3_reset(pRange);
         3027  +
         3028  +    if( bOk ){
         3029  +      int iIdx = 0;
         3030  +      sqlite3_stmt *pUpdate1;
         3031  +      sqlite3_stmt *pUpdate2;
         3032  +
         3033  +      if( rc==SQLITE_OK ){
         3034  +        rc = fts3SqlStmt(p, SQL_UPDATE_LEVEL_IDX, &pUpdate1, 0);
         3035  +      }
         3036  +      if( rc==SQLITE_OK ){
         3037  +        rc = fts3SqlStmt(p, SQL_UPDATE_LEVEL, &pUpdate2, 0);
         3038  +      }
         3039  +
         3040  +      if( rc==SQLITE_OK ){
         3041  +        sqlite3_bind_int(pRange, 1, iAbsLevel);
         3042  +        while( SQLITE_ROW==sqlite3_step(pRange) ){
         3043  +          sqlite3_bind_int(pUpdate1, 1, iIdx++);
         3044  +          sqlite3_bind_int(pUpdate1, 2, sqlite3_column_int(pRange, 0));
         3045  +          sqlite3_bind_int(pUpdate1, 3, sqlite3_column_int(pRange, 1));
         3046  +          sqlite3_step(pUpdate1);
         3047  +          rc = sqlite3_reset(pUpdate1);
         3048  +          if( rc!=SQLITE_OK ){
         3049  +            sqlite3_reset(pRange);
         3050  +            break;
         3051  +          }
         3052  +        }
         3053  +      }
         3054  +      if( rc==SQLITE_OK ){
         3055  +        rc = sqlite3_reset(pRange);
         3056  +      }
         3057  +
         3058  +      if( rc==SQLITE_OK ){
         3059  +        sqlite3_bind_int(pUpdate2, 1, iAbsLevel);
         3060  +        sqlite3_step(pUpdate2);
         3061  +        rc = sqlite3_reset(pUpdate2);
         3062  +      }
         3063  +    }
         3064  +  }
         3065  +
         3066  +
         3067  +  return rc;
         3068  +}
  2944   3069   
  2945   3070   /*
  2946   3071   ** Merge all level iLevel segments in the database into a single 
  2947   3072   ** iLevel+1 segment. Or, if iLevel<0, merge all segments into a
  2948   3073   ** single segment with a level equal to the numerically largest level 
  2949   3074   ** currently present in the database.
  2950   3075   **
................................................................................
  3022   3147     if( iLevel!=FTS3_SEGCURSOR_PENDING ){
  3023   3148       rc = fts3DeleteSegdir(
  3024   3149           p, iLangid, iIndex, iLevel, csr.apSegment, csr.nSegment
  3025   3150       );
  3026   3151       if( rc!=SQLITE_OK ) goto finished;
  3027   3152     }
  3028   3153     rc = fts3SegWriterFlush(p, pWriter, iNewLevel, iIdx);
         3154  +  if( rc==SQLITE_OK ){
         3155  +    rc = fts3PromoteSegments(p, iNewLevel, pWriter->nLeafData);
         3156  +  }
  3029   3157   
  3030   3158    finished:
  3031   3159     fts3SegWriterFree(pWriter);
  3032   3160     sqlite3Fts3SegReaderFinish(&csr);
  3033   3161     return rc;
  3034   3162   }
  3035   3163   
  3036   3164   
  3037   3165   /* 
  3038         -** Flush the contents of pendingTerms to level 0 segments.
         3166  +** Flush the contents of pendingTerms to level 0 segments. 
  3039   3167   */
  3040   3168   int sqlite3Fts3PendingTermsFlush(Fts3Table *p){
  3041   3169     int rc = SQLITE_OK;
  3042   3170     int i;
  3043   3171           
  3044   3172     for(i=0; rc==SQLITE_OK && i<p->nIndex; i++){
  3045   3173       rc = fts3SegmentMerge(p, p->iPrevLangid, i, FTS3_SEGCURSOR_PENDING);
................................................................................
  3422   3550   struct IncrmergeWriter {
  3423   3551     int nLeafEst;                   /* Space allocated for leaf blocks */
  3424   3552     int nWork;                      /* Number of leaf pages flushed */
  3425   3553     sqlite3_int64 iAbsLevel;        /* Absolute level of input segments */
  3426   3554     int iIdx;                       /* Index of *output* segment in iAbsLevel+1 */
  3427   3555     sqlite3_int64 iStart;           /* Block number of first allocated block */
  3428   3556     sqlite3_int64 iEnd;             /* Block number of last allocated block */
         3557  +  sqlite3_int64 nLeafData;        /* Bytes of leaf page data so far */
  3429   3558     NodeWriter aNodeWriter[FTS_MAX_APPENDABLE_HEIGHT];
  3430   3559   };
  3431   3560   
  3432   3561   /*
  3433   3562   ** An object of the following type is used to read data from a single
  3434   3563   ** FTS segment node. See the following functions:
  3435   3564   **
................................................................................
  3760   3889   
  3761   3890       nSuffix = nTerm;
  3762   3891       nSpace  = 1;
  3763   3892       nSpace += sqlite3Fts3VarintLen(nSuffix) + nSuffix;
  3764   3893       nSpace += sqlite3Fts3VarintLen(nDoclist) + nDoclist;
  3765   3894     }
  3766   3895   
         3896  +  pWriter->nLeafData += nSpace;
  3767   3897     blobGrowBuffer(&pLeaf->block, pLeaf->block.n + nSpace, &rc);
  3768         -
  3769   3898     if( rc==SQLITE_OK ){
  3770   3899       if( pLeaf->block.n==0 ){
  3771   3900         pLeaf->block.n = 1;
  3772   3901         pLeaf->block.a[0] = '\0';
  3773   3902       }
  3774   3903       rc = fts3AppendToNode(
  3775   3904           &pLeaf->block, &pLeaf->key, zTerm, nTerm, aDoclist, nDoclist
................................................................................
  3860   3989     if( rc==SQLITE_OK ){
  3861   3990       rc = fts3WriteSegdir(p, 
  3862   3991           pWriter->iAbsLevel+1,               /* level */
  3863   3992           pWriter->iIdx,                      /* idx */
  3864   3993           pWriter->iStart,                    /* start_block */
  3865   3994           pWriter->aNodeWriter[0].iBlock,     /* leaves_end_block */
  3866   3995           pWriter->iEnd,                      /* end_block */
         3996  +        pWriter->nLeafData,                 /* end_block */
  3867   3997           pRoot->block.a, pRoot->block.n      /* root */
  3868   3998       );
  3869   3999     }
  3870   4000     sqlite3_free(pRoot->block.a);
  3871   4001     sqlite3_free(pRoot->key.a);
  3872   4002   
  3873   4003     *pRc = rc;
................................................................................
  3961   4091   
  3962   4092       /* Read the %_segdir entry for index iIdx absolute level (iAbsLevel+1) */
  3963   4093       sqlite3_bind_int64(pSelect, 1, iAbsLevel+1);
  3964   4094       sqlite3_bind_int(pSelect, 2, iIdx);
  3965   4095       if( sqlite3_step(pSelect)==SQLITE_ROW ){
  3966   4096         iStart = sqlite3_column_int64(pSelect, 1);
  3967   4097         iLeafEnd = sqlite3_column_int64(pSelect, 2);
  3968         -      iEnd = sqlite3_column_int64(pSelect, 3);
         4098  +      fts3ReadEndBlockField(pSelect, 3, &iEnd, &pWriter->nLeafData);
  3969   4099         nRoot = sqlite3_column_bytes(pSelect, 4);
  3970   4100         aRoot = sqlite3_column_blob(pSelect, 4);
  3971   4101       }else{
  3972   4102         return sqlite3_reset(pSelect);
  3973   4103       }
  3974   4104   
  3975   4105       /* Check for the zero-length marker in the %_segments table */
................................................................................
  4562   4692     return SQLITE_OK;
  4563   4693   }
  4564   4694   
  4565   4695   
  4566   4696   /*
  4567   4697   ** Attempt an incremental merge that writes nMerge leaf blocks.
  4568   4698   **
  4569         -** Incremental merges happen nMin segments at a time. The two
  4570         -** segments to be merged are the nMin oldest segments (the ones with
  4571         -** the smallest indexes) in the highest level that contains at least
  4572         -** nMin segments. Multiple merges might occur in an attempt to write the 
  4573         -** quota of nMerge leaf blocks.
         4699  +** Incremental merges happen nMin segments at a time. The segments 
         4700  +** to be merged are the nMin oldest segments (the ones with the smallest 
         4701  +** values for the _segdir.idx field) in the highest level that contains 
         4702  +** at least nMin segments. Multiple merges might occur in an attempt to 
         4703  +** write the quota of nMerge leaf blocks.
  4574   4704   */
  4575   4705   int sqlite3Fts3Incrmerge(Fts3Table *p, int nMerge, int nMin){
  4576   4706     int rc;                         /* Return code */
  4577   4707     int nRem = nMerge;              /* Number of leaf pages yet to  be written */
  4578   4708     Fts3MultiSegReader *pCsr;       /* Cursor used to read input data */
  4579   4709     Fts3SegFilter *pFilter;         /* Filter used with cursor pCsr */
  4580   4710     IncrmergeWriter *pWriter;       /* Writer object */
................................................................................
  4683   4813               bDirtyHint = 1;
  4684   4814               fts3IncrmergeHintPush(&hint, iAbsLevel, nSeg, &rc);
  4685   4815             }
  4686   4816           }
  4687   4817         }
  4688   4818   
  4689   4819         fts3IncrmergeRelease(p, pWriter, &rc);
         4820  +      if( nSeg==0 ){
         4821  +        fts3PromoteSegments(p, iAbsLevel+1, pWriter->nLeafData);
         4822  +      }
  4690   4823       }
  4691   4824   
  4692   4825       sqlite3Fts3SegReaderFinish(pCsr);
  4693   4826     }
  4694   4827   
  4695   4828     /* Write the hint values into the %_stat table for the next incr-merger */
  4696   4829     if( bDirtyHint && rc==SQLITE_OK ){

Added test/fts4growth.test.

            1  +# 2014 May 12
            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 FTS4 module.
           13  +#
           14  +#
           15  +
           16  +set testdir [file dirname $argv0]
           17  +source $testdir/tester.tcl
           18  +set testprefix fts4growth
           19  +
           20  +# If SQLITE_ENABLE_FTS3 is defined, omit this file.
           21  +ifcapable !fts3 {
           22  +  finish_test
           23  +  return
           24  +}
           25  +
           26  +source $testdir/genesis.tcl
           27  +
           28  +do_execsql_test 1.1 { CREATE VIRTUAL TABLE x1 USING fts3; }
           29  +
           30  +do_test 1.2 {
           31  +  foreach L {
           32  +    {"See here, young man," said Mulga Bill, "from Walgett to the sea,}
           33  +    {From Conroy's Gap to Castlereagh, there's none can ride like me.}
           34  +    {I'm good all round at everything as everybody knows,}
           35  +    {Although I'm not the one to talk -- I hate a man that blows.}
           36  +  } {
           37  +    execsql { INSERT INTO x1 VALUES($L) }
           38  +  }
           39  +  execsql { SELECT end_block, length(root) FROM x1_segdir }
           40  +} {{0 114} 114 {0 118} 118 {0 95} 95 {0 115} 115}
           41  +
           42  +do_execsql_test 1.3 {
           43  +  INSERT INTO x1(x1) VALUES('optimize');
           44  +  SELECT level, end_block, length(root) FROM x1_segdir;
           45  +} {0 {0 394} 394}
           46  +
           47  +do_test 1.4 {
           48  +  foreach L {
           49  +    {But riding is my special gift, my chiefest, sole delight;}
           50  +    {Just ask a wild duck can it swim, a wildcat can it fight.}
           51  +    {There's nothing clothed in hair or hide, or built of flesh or steel,}
           52  +    {There's nothing walks or jumps, or runs, on axle, hoof, or wheel,}
           53  +    {But what I'll sit, while hide will hold and girths and straps are tight:}
           54  +    {I'll ride this here two-wheeled concern right straight away at sight."}
           55  +  } {
           56  +    execsql { INSERT INTO x1 VALUES($L) }
           57  +  }
           58  +  execsql { 
           59  +    INSERT INTO x1(x1) VALUES('merge=4,4');
           60  +    SELECT level, end_block, length(root) FROM x1_segdir;
           61  +  }
           62  +} {0 {0 110} 110 0 {0 132} 132 0 {0 129} 129 1 {128 658} 2}
           63  +
           64  +do_execsql_test 1.5 {
           65  +  SELECT length(block) FROM x1_segments;
           66  +} {658 {}}
           67  +
           68  +do_test 1.6 {
           69  +  foreach L {
           70  +    {'Twas Mulga Bill, from Eaglehawk, that sought his own abode,}
           71  +    {That perched above Dead Man's Creek, beside the mountain road.}
           72  +    {He turned the cycle down the hill and mounted for the fray,}
           73  +    {But 'ere he'd gone a dozen yards it bolted clean away.}
           74  +    {It left the track, and through the trees, just like a silver steak,}
           75  +    {It whistled down the awful slope towards the Dead Man's Creek.}
           76  +    {It shaved a stump by half an inch, it dodged a big white-box:}
           77  +    {The very wallaroos in fright went scrambling up the rocks,}
           78  +    {The wombats hiding in their caves dug deeper underground,}
           79  +    {As Mulga Bill, as white as chalk, sat tight to every bound.}
           80  +    {It struck a stone and gave a spring that cleared a fallen tree,}
           81  +    {It raced beside a precipice as close as close could be;}
           82  +    {And then as Mulga Bill let out one last despairing shriek}
           83  +    {It made a leap of twenty feet into the Dead Man's Creek.}
           84  +  } {
           85  +    execsql { INSERT INTO x1 VALUES($L) }
           86  +  }
           87  +  execsql { 
           88  +    SELECT level, end_block, length(root) FROM x1_segdir;
           89  +  }
           90  +} {1 {128 658} 2 1 {130 1377} 6 0 {0 117} 117}
           91  +
           92  +do_execsql_test 1.7 {
           93  +  SELECT sum(length(block)) FROM x1_segments WHERE blockid IN (129, 130);
           94  +} {1377}
           95  +
           96  +#-------------------------------------------------------------------------
           97  +#
           98  +do_execsql_test 2.1 { 
           99  +  CREATE TABLE t1(docid, words);
          100  +  CREATE VIRTUAL TABLE x2 USING fts4;
          101  +}
          102  +fts_kjv_genesis 
          103  +do_test 2.2 {
          104  +  foreach id [db eval {SELECT docid FROM t1}] {
          105  +    execsql {
          106  +      INSERT INTO x2(docid, content) SELECT $id, words FROM t1 WHERE docid=$id
          107  +    }
          108  +  }
          109  +  foreach id [db eval {SELECT docid FROM t1}] {
          110  +    execsql {
          111  +      INSERT INTO x2(docid, content) SELECT NULL, words FROM t1 WHERE docid=$id
          112  +    }
          113  +    if {[db one {SELECT count(*) FROM x2_segdir WHERE level<2}]==2} break
          114  +  }
          115  +} {}
          116  +
          117  +do_execsql_test 2.3 { 
          118  +  SELECT count(*) FROM x2_segdir WHERE level=2;
          119  +  SELECT count(*) FROM x2_segdir WHERE level=3;
          120  +} {6 0}
          121  +
          122  +do_execsql_test 2.4 { 
          123  +  INSERT INTO x2(x2) VALUES('merge=4,4');
          124  +  SELECT count(*) FROM x2_segdir WHERE level=2;
          125  +  SELECT count(*) FROM x2_segdir WHERE level=3;
          126  +} {6 1}
          127  +
          128  +do_execsql_test 2.5 { 
          129  +  SELECT end_block FROM x2_segdir WHERE level=3;
          130  +  INSERT INTO x2(x2) VALUES('merge=4,4');
          131  +  SELECT end_block FROM x2_segdir WHERE level=3;
          132  +  INSERT INTO x2(x2) VALUES('merge=4,4');
          133  +  SELECT end_block FROM x2_segdir WHERE level=3;
          134  +} {{3828 3430} {3828 10191} {3828 14109}}
          135  +
          136  +do_execsql_test 2.6 {
          137  +  SELECT sum(length(block)) FROM x2_segdir, x2_segments WHERE 
          138  +    blockid BETWEEN start_block AND leaves_end_block
          139  +    AND level=3
          140  +} {14109}
          141  +
          142  +do_execsql_test 2.7 { 
          143  +  INSERT INTO x2(x2) VALUES('merge=1000,4');
          144  +  SELECT end_block FROM x2_segdir WHERE level=3;
          145  +} {{3828 86120}}
          146  +
          147  +do_execsql_test 2.8 {
          148  +  SELECT sum(length(block)) FROM x2_segdir, x2_segments WHERE 
          149  +    blockid BETWEEN start_block AND leaves_end_block
          150  +    AND level=3
          151  +} {86120}
          152  +
          153  +#--------------------------------------------------------------------------
          154  +
          155  +do_execsql_test 3.1 {
          156  +  DROP TABLE IF EXISTS x2;
          157  +  DROP TABLE IF EXISTS t1;
          158  +  CREATE TABLE t1(docid, words);
          159  +  CREATE VIRTUAL TABLE x2 USING fts4;
          160  +}
          161  +fts_kjv_genesis 
          162  +
          163  +proc t1_to_x2 {} {
          164  +  foreach id [db eval {SELECT docid FROM t1 LIMIT 2}] {
          165  +    execsql {
          166  +      DELETE FROM x2 WHERE docid=$id;
          167  +      INSERT INTO x2(docid, content) SELECT $id, words FROM t1 WHERE docid=$id;
          168  +    }
          169  +  }
          170  +}
          171  +
          172  +#do_test 3.2 {
          173  +  #t1_to_x2
          174  +  #execsql {SELECT level, count(*) FROM x2_segdir GROUP BY level}
          175  +#} {0 13 1 15 2 5}
          176  +
          177  +#proc second {x} { lindex $x 1 }
          178  +#db func second second
          179  +#for {set i 0} {$i <1000} {incr i} {
          180  +#  t1_to_x2
          181  +#  db eval {
          182  +#    SELECT level, group_concat( second(end_block), ' ' ) AS c FROM x2_segdir GROUP BY level;
          183  +#  } {
          184  +#    puts "$i.$level: $c"
          185  +#  }
          186  +#}
          187  +
          188  +
          189  +finish_test
          190  +
          191  +