/ Check-in [21491a9b]
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:Fix various problems to do with segment promotion. Add test file fts4growth2.test, containing tests to check that the FTS index does not grow indefinitely as the table is updated. Allow the user to configure the number of segments merged simultaneously by the automerge option.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | fts4-experimental
Files: files | file ages | folders
SHA1: 21491a9bc686e63bec32f1a67103622f1160a2f9
User & Date: dan 2014-05-14 15:58:47
Original Comment: Fix various problems to do with segment promotion. Add test file fts4growth2.test, containing tests to check that the FTS index does not grow indefinitely as the table is updated.
Context
2014-05-14
18:45
Fix a problem to do with loading the value of the FTS automerge setting from the database. check-in: 7268119f user: dan tags: fts4-experimental
15:58
Fix various problems to do with segment promotion. Add test file fts4growth2.test, containing tests to check that the FTS index does not grow indefinitely as the table is updated. Allow the user to configure the number of segments merged simultaneously by the automerge option. check-in: 21491a9b user: dan tags: fts4-experimental
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
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to ext/fts3/fts3.c.

1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
....
3298
3299
3300
3301
3302
3303
3304

3305


3306
3307
3308
3309
3310
3311
3312
3313
3314
3315
3316
3317
3318
3319
3320
  p->azColumn = (char **)&p[1];
  p->pTokenizer = pTokenizer;
  p->nMaxPendingData = FTS3_MAX_PENDING_DATA;
  p->bHasDocsize = (isFts4 && bNoDocsize==0);
  p->bHasStat = isFts4;
  p->bFts4 = isFts4;
  p->bDescIdx = bDescIdx;
  p->bAutoincrmerge = 0xff;   /* 0xff means setting unknown */
  p->zContentTbl = zContent;
  p->zLanguageid = zLanguageid;
  zContent = 0;
  zLanguageid = 0;
  TESTONLY( p->inTransaction = -1 );
  TESTONLY( p->mxSavepoint = -1 );

................................................................................
  ** segments.
  */
  const u32 nMinMerge = 64;       /* Minimum amount of incr-merge work to do */

  Fts3Table *p = (Fts3Table*)pVtab;
  int rc = sqlite3Fts3PendingTermsFlush(p);


  if( rc==SQLITE_OK && p->bAutoincrmerge==1 && p->nLeafAdd>(nMinMerge/16) ){


    int mxLevel = 0;              /* Maximum relative level value in db */
    int A;                        /* Incr-merge parameter A */

    rc = sqlite3Fts3MaxLevel(p, &mxLevel);
    assert( rc==SQLITE_OK || mxLevel==0 );
    A = p->nLeafAdd * mxLevel;
    A += (A/2);
    if( A>(int)nMinMerge ) rc = sqlite3Fts3Incrmerge(p, A, 8);
  }
  sqlite3Fts3SegmentsClose(p);
  return rc;
}

/*
** If it is currently unknown whether or not the FTS table has an %_stat







|







 







>
|
>
>







|







1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
....
3298
3299
3300
3301
3302
3303
3304
3305
3306
3307
3308
3309
3310
3311
3312
3313
3314
3315
3316
3317
3318
3319
3320
3321
3322
3323
  p->azColumn = (char **)&p[1];
  p->pTokenizer = pTokenizer;
  p->nMaxPendingData = FTS3_MAX_PENDING_DATA;
  p->bHasDocsize = (isFts4 && bNoDocsize==0);
  p->bHasStat = isFts4;
  p->bFts4 = isFts4;
  p->bDescIdx = bDescIdx;
  p->nAutoincrmerge = 0xff;   /* 0xff means setting unknown */
  p->zContentTbl = zContent;
  p->zLanguageid = zLanguageid;
  zContent = 0;
  zLanguageid = 0;
  TESTONLY( p->inTransaction = -1 );
  TESTONLY( p->mxSavepoint = -1 );

................................................................................
  ** segments.
  */
  const u32 nMinMerge = 64;       /* Minimum amount of incr-merge work to do */

  Fts3Table *p = (Fts3Table*)pVtab;
  int rc = sqlite3Fts3PendingTermsFlush(p);

  if( rc==SQLITE_OK 
   && p->nLeafAdd>(nMinMerge/16) 
   && p->nAutoincrmerge && p->nAutoincrmerge!=0xff
  ){
    int mxLevel = 0;              /* Maximum relative level value in db */
    int A;                        /* Incr-merge parameter A */

    rc = sqlite3Fts3MaxLevel(p, &mxLevel);
    assert( rc==SQLITE_OK || mxLevel==0 );
    A = p->nLeafAdd * mxLevel;
    A += (A/2);
    if( A>(int)nMinMerge ) rc = sqlite3Fts3Incrmerge(p, A, p->nAutoincrmerge);
  }
  sqlite3Fts3SegmentsClose(p);
  return rc;
}

/*
** If it is currently unknown whether or not the FTS table has an %_stat

Changes to ext/fts3/fts3Int.h.

206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
  const char *zName;              /* virtual table name */
  int nColumn;                    /* number of named columns in virtual table */
  char **azColumn;                /* column names.  malloced */
  u8 *abNotindexed;               /* True for 'notindexed' columns */
  sqlite3_tokenizer *pTokenizer;  /* tokenizer for inserts and queries */
  char *zContentTbl;              /* content=xxx option, or NULL */
  char *zLanguageid;              /* languageid=xxx option, or NULL */
  u8 bAutoincrmerge;              /* True if automerge=1 */
  u32 nLeafAdd;                   /* Number of leaf blocks added this trans */

  /* Precompiled statements used by the implementation. Each of these 
  ** statements is run and reset within a single virtual table API call. 
  */
  sqlite3_stmt *aStmt[40];








|







206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
  const char *zName;              /* virtual table name */
  int nColumn;                    /* number of named columns in virtual table */
  char **azColumn;                /* column names.  malloced */
  u8 *abNotindexed;               /* True for 'notindexed' columns */
  sqlite3_tokenizer *pTokenizer;  /* tokenizer for inserts and queries */
  char *zContentTbl;              /* content=xxx option, or NULL */
  char *zLanguageid;              /* languageid=xxx option, or NULL */
  int nAutoincrmerge;             /* Value configured by 'automerge' */
  u32 nLeafAdd;                   /* Number of leaf blocks added this trans */

  /* Precompiled statements used by the implementation. Each of these 
  ** statements is run and reset within a single virtual table API call. 
  */
  sqlite3_stmt *aStmt[40];

Changes to ext/fts3/fts3_write.c.

2967
2968
2969
2970
2971
2972
2973






2974
2975
2976
2977
2978
2979
2980
2981
2982
2983

2984
2985
2986
2987
2988
2989
2990




2991
2992
2993
2994
2995
2996
2997
2998
2999
3000
3001
....
3007
3008
3009
3010
3011
3012
3013
3014
3015

3016





3017
3018
3019
3020
3021
3022





3023
3024
3025

3026
3027
3028
3029
3030
3031
3032
....
3035
3036
3037
3038
3039
3040
3041










3042
3043
3044
3045
3046
3047
3048
....
3052
3053
3054
3055
3056
3057
3058

3059
3060
3061
3062
3063
3064
3065
....
3184
3185
3186
3187
3188
3189
3190
3191
3192
3193
3194
3195
3196
3197
3198

3199
3200
3201
3202
3203
3204
3205
....
4101
4102
4103
4104
4105
4106
4107



4108
4109
4110
4111
4112
4113
4114
....
4824
4825
4826
4827
4828
4829
4830



4831
4832
4833
4834
4835
4836
4837
....
4914
4915
4916
4917
4918
4919
4920
4921



4922
4923
4924
4925
4926
4927
4928
4929
4930
4931
4932
4933
4934
4935
4936
4937
    pCsr->aBuffer = 0;
  }
}

/*
** Decode the "end_block" field, selected by column iCol of the SELECT 
** statement passed as the first argument. 






*/
static void fts3ReadEndBlockField(
  sqlite3_stmt *pStmt, 
  int iCol, 
  i64 *piEndBlock, 
  i64 *pnByte
){
  const unsigned char *zText = sqlite3_column_text(pStmt, iCol);
  if( zText ){
    int i;

    i64 iVal = 0;
    for(i=0; zText[i]>='0' && zText[i]<='9'; i++){
      iVal = iVal*10 + (zText[i] - '0');
    }
    *piEndBlock = iVal;
    while( zText[i]==' ' ) i++;
    iVal = 0;




    for(/* no-op */; zText[i]>='0' && zText[i]<='9'; i++){
      iVal = iVal*10 + (zText[i] - '0');
    }
    *pnByte = iVal;
  }
}


/*
** A segment of size nByte bytes has just been written to absolute level
** iAbsLevel. Promote any segments that should be promoted as a result.
................................................................................
){
  int rc = SQLITE_OK;
  sqlite3_stmt *pRange;

  rc = fts3SqlStmt(p, SQL_SELECT_LEVEL_RANGE2, &pRange, 0);

  if( rc==SQLITE_OK ){
    int bOk = 1;
    int iLast = (iAbsLevel/FTS3_SEGDIR_MAXLEVEL + 1) * FTS3_SEGDIR_MAXLEVEL - 1;







    sqlite3_bind_int(pRange, 1, iAbsLevel+1);
    sqlite3_bind_int(pRange, 2, iLast);
    while( SQLITE_ROW==sqlite3_step(pRange) ){
      i64 nSize, dummy;
      fts3ReadEndBlockField(pRange, 2, &dummy, &nSize);
      if( nSize>nByte ){





        bOk = 0;
        break;
      }

    }
    rc = sqlite3_reset(pRange);

    if( bOk ){
      int iIdx = 0;
      sqlite3_stmt *pUpdate1;
      sqlite3_stmt *pUpdate2;
................................................................................
        rc = fts3SqlStmt(p, SQL_UPDATE_LEVEL_IDX, &pUpdate1, 0);
      }
      if( rc==SQLITE_OK ){
        rc = fts3SqlStmt(p, SQL_UPDATE_LEVEL, &pUpdate2, 0);
      }

      if( rc==SQLITE_OK ){










        sqlite3_bind_int(pRange, 1, iAbsLevel);
        while( SQLITE_ROW==sqlite3_step(pRange) ){
          sqlite3_bind_int(pUpdate1, 1, iIdx++);
          sqlite3_bind_int(pUpdate1, 2, sqlite3_column_int(pRange, 0));
          sqlite3_bind_int(pUpdate1, 3, sqlite3_column_int(pRange, 1));
          sqlite3_step(pUpdate1);
          rc = sqlite3_reset(pUpdate1);
................................................................................
          }
        }
      }
      if( rc==SQLITE_OK ){
        rc = sqlite3_reset(pRange);
      }


      if( rc==SQLITE_OK ){
        sqlite3_bind_int(pUpdate2, 1, iAbsLevel);
        sqlite3_step(pUpdate2);
        rc = sqlite3_reset(pUpdate2);
      }
    }
  }
................................................................................
  }
  sqlite3Fts3PendingTermsClear(p);

  /* Determine the auto-incr-merge setting if unknown.  If enabled,
  ** estimate the number of leaf blocks of content to be written
  */
  if( rc==SQLITE_OK && p->bHasStat
   && p->bAutoincrmerge==0xff && p->nLeafAdd>0
  ){
    sqlite3_stmt *pStmt = 0;
    rc = fts3SqlStmt(p, SQL_SELECT_STAT, &pStmt, 0);
    if( rc==SQLITE_OK ){
      sqlite3_bind_int(pStmt, 1, FTS_STAT_AUTOINCRMERGE);
      rc = sqlite3_step(pStmt);
      p->bAutoincrmerge = (rc==SQLITE_ROW && sqlite3_column_int(pStmt, 0));

      rc = sqlite3_reset(pStmt);
    }
  }
  return rc;
}

/*
................................................................................
    /* Read the %_segdir entry for index iIdx absolute level (iAbsLevel+1) */
    sqlite3_bind_int64(pSelect, 1, iAbsLevel+1);
    sqlite3_bind_int(pSelect, 2, iIdx);
    if( sqlite3_step(pSelect)==SQLITE_ROW ){
      iStart = sqlite3_column_int64(pSelect, 1);
      iLeafEnd = sqlite3_column_int64(pSelect, 2);
      fts3ReadEndBlockField(pSelect, 3, &iEnd, &pWriter->nLeafData);



      nRoot = sqlite3_column_bytes(pSelect, 4);
      aRoot = sqlite3_column_blob(pSelect, 4);
    }else{
      return sqlite3_reset(pSelect);
    }

    /* Check for the zero-length marker in the %_segments table */
................................................................................
          if( nSeg!=0 ){
            bDirtyHint = 1;
            fts3IncrmergeHintPush(&hint, iAbsLevel, nSeg, &rc);
          }
        }
      }




      fts3IncrmergeRelease(p, pWriter, &rc);
      if( nSeg==0 ){
        fts3PromoteSegments(p, iAbsLevel+1, pWriter->nLeafData);
      }
    }

    sqlite3Fts3SegReaderFinish(pCsr);
................................................................................
*/
static int fts3DoAutoincrmerge(
  Fts3Table *p,                   /* FTS3 table handle */
  const char *zParam              /* Nul-terminated string containing boolean */
){
  int rc = SQLITE_OK;
  sqlite3_stmt *pStmt = 0;
  p->bAutoincrmerge = fts3Getint(&zParam)!=0;



  if( !p->bHasStat ){
    assert( p->bFts4==0 );
    sqlite3Fts3CreateStatTable(&rc, p);
    if( rc ) return rc;
  }
  rc = fts3SqlStmt(p, SQL_REPLACE_STAT, &pStmt, 0);
  if( rc ) return rc;
  sqlite3_bind_int(pStmt, 1, FTS_STAT_AUTOINCRMERGE);
  sqlite3_bind_int(pStmt, 2, p->bAutoincrmerge);
  sqlite3_step(pStmt);
  rc = sqlite3_reset(pStmt);
  return rc;
}

/*
** Return a 64-bit checksum for the FTS index entry specified by the







>
>
>
>
>
>




|





>







>
>
>
>



|







 







|

>

>
>
>
>
>





|
>
>
>
>
>



>







 







>
>
>
>
>
>
>
>
>
>







 







>







 







|






|
>







 







>
>
>







 







>
>
>







 







|
>
>
>








|







2967
2968
2969
2970
2971
2972
2973
2974
2975
2976
2977
2978
2979
2980
2981
2982
2983
2984
2985
2986
2987
2988
2989
2990
2991
2992
2993
2994
2995
2996
2997
2998
2999
3000
3001
3002
3003
3004
3005
3006
3007
3008
3009
3010
3011
3012
....
3018
3019
3020
3021
3022
3023
3024
3025
3026
3027
3028
3029
3030
3031
3032
3033
3034
3035
3036
3037
3038
3039
3040
3041
3042
3043
3044
3045
3046
3047
3048
3049
3050
3051
3052
3053
3054
3055
....
3058
3059
3060
3061
3062
3063
3064
3065
3066
3067
3068
3069
3070
3071
3072
3073
3074
3075
3076
3077
3078
3079
3080
3081
....
3085
3086
3087
3088
3089
3090
3091
3092
3093
3094
3095
3096
3097
3098
3099
....
3218
3219
3220
3221
3222
3223
3224
3225
3226
3227
3228
3229
3230
3231
3232
3233
3234
3235
3236
3237
3238
3239
3240
....
4136
4137
4138
4139
4140
4141
4142
4143
4144
4145
4146
4147
4148
4149
4150
4151
4152
....
4862
4863
4864
4865
4866
4867
4868
4869
4870
4871
4872
4873
4874
4875
4876
4877
4878
....
4955
4956
4957
4958
4959
4960
4961
4962
4963
4964
4965
4966
4967
4968
4969
4970
4971
4972
4973
4974
4975
4976
4977
4978
4979
4980
4981
    pCsr->aBuffer = 0;
  }
}

/*
** Decode the "end_block" field, selected by column iCol of the SELECT 
** statement passed as the first argument. 
**
** The "end_block" field may contain either an integer, or a text field
** containing the text representation of two non-negative integers separated 
** by one or more space (0x20) characters. In the first case, set *piEndBlock 
** to the integer value and *pnByte to zero before returning. In the second, 
** set *piEndBlock to the first value and *pnByte to the second.
*/
static void fts3ReadEndBlockField(
  sqlite3_stmt *pStmt, 
  int iCol, 
  i64 *piEndBlock,
  i64 *pnByte
){
  const unsigned char *zText = sqlite3_column_text(pStmt, iCol);
  if( zText ){
    int i;
    int iMul = 1;
    i64 iVal = 0;
    for(i=0; zText[i]>='0' && zText[i]<='9'; i++){
      iVal = iVal*10 + (zText[i] - '0');
    }
    *piEndBlock = iVal;
    while( zText[i]==' ' ) i++;
    iVal = 0;
    if( zText[i]=='-' ){
      i++;
      iMul = -1;
    }
    for(/* no-op */; zText[i]>='0' && zText[i]<='9'; i++){
      iVal = iVal*10 + (zText[i] - '0');
    }
    *pnByte = (iVal * (i64)iMul);
  }
}


/*
** A segment of size nByte bytes has just been written to absolute level
** iAbsLevel. Promote any segments that should be promoted as a result.
................................................................................
){
  int rc = SQLITE_OK;
  sqlite3_stmt *pRange;

  rc = fts3SqlStmt(p, SQL_SELECT_LEVEL_RANGE2, &pRange, 0);

  if( rc==SQLITE_OK ){
    int bOk = 0;
    int iLast = (iAbsLevel/FTS3_SEGDIR_MAXLEVEL + 1) * FTS3_SEGDIR_MAXLEVEL - 1;
    i64 nLimit = (nByte*3)/2;

    /* Loop through all entries in the %_segdir table corresponding to 
    ** segments in this index on levels greater than iAbsLevel. If there is
    ** at least one such segment, and it is possible to determine that all 
    ** such segments are smaller than nLimit bytes in size, they will be 
    ** promoted to level iAbsLevel.  */
    sqlite3_bind_int(pRange, 1, iAbsLevel+1);
    sqlite3_bind_int(pRange, 2, iLast);
    while( SQLITE_ROW==sqlite3_step(pRange) ){
      i64 nSize, dummy;
      fts3ReadEndBlockField(pRange, 2, &dummy, &nSize);
      if( nSize<=0 || nSize>nLimit ){
        /* If nSize==0, then the %_segdir.end_block field does not not 
        ** contain a size value. This happens if it was written by an
        ** old version of FTS. In this case it is not possible to determine
        ** the size of the segment, and so segment promotion does not
        ** take place.  */
        bOk = 0;
        break;
      }
      bOk = 1;
    }
    rc = sqlite3_reset(pRange);

    if( bOk ){
      int iIdx = 0;
      sqlite3_stmt *pUpdate1;
      sqlite3_stmt *pUpdate2;
................................................................................
        rc = fts3SqlStmt(p, SQL_UPDATE_LEVEL_IDX, &pUpdate1, 0);
      }
      if( rc==SQLITE_OK ){
        rc = fts3SqlStmt(p, SQL_UPDATE_LEVEL, &pUpdate2, 0);
      }

      if( rc==SQLITE_OK ){

        /* Loop through all %_segdir entries for segments in this index with
        ** levels equal to or greater than iAbsLevel. As each entry is visited,
        ** updated it to set (level = -1) and (idx = N), where N is 0 for the
        ** oldest segment in the range, 1 for the next oldest, and so on.
        **
        ** In other words, move all segments being promoted to level -1,
        ** setting the "idx" fields as appropriate to keep them in the same
        ** order. The contents of level -1 (which is never used, except
        ** transiently here), will be moved back to level iAbsLevel below.  */
        sqlite3_bind_int(pRange, 1, iAbsLevel);
        while( SQLITE_ROW==sqlite3_step(pRange) ){
          sqlite3_bind_int(pUpdate1, 1, iIdx++);
          sqlite3_bind_int(pUpdate1, 2, sqlite3_column_int(pRange, 0));
          sqlite3_bind_int(pUpdate1, 3, sqlite3_column_int(pRange, 1));
          sqlite3_step(pUpdate1);
          rc = sqlite3_reset(pUpdate1);
................................................................................
          }
        }
      }
      if( rc==SQLITE_OK ){
        rc = sqlite3_reset(pRange);
      }

      /* Move level -1 to level iAbsLevel */
      if( rc==SQLITE_OK ){
        sqlite3_bind_int(pUpdate2, 1, iAbsLevel);
        sqlite3_step(pUpdate2);
        rc = sqlite3_reset(pUpdate2);
      }
    }
  }
................................................................................
  }
  sqlite3Fts3PendingTermsClear(p);

  /* Determine the auto-incr-merge setting if unknown.  If enabled,
  ** estimate the number of leaf blocks of content to be written
  */
  if( rc==SQLITE_OK && p->bHasStat
   && p->nAutoincrmerge==0xff && p->nLeafAdd>0
  ){
    sqlite3_stmt *pStmt = 0;
    rc = fts3SqlStmt(p, SQL_SELECT_STAT, &pStmt, 0);
    if( rc==SQLITE_OK ){
      sqlite3_bind_int(pStmt, 1, FTS_STAT_AUTOINCRMERGE);
      rc = sqlite3_step(pStmt);
      p->nAutoincrmerge = (rc==SQLITE_ROW && sqlite3_column_int(pStmt, 0));
      if( p->nAutoincrmerge==1 ) p->nAutoincrmerge = 8;
      rc = sqlite3_reset(pStmt);
    }
  }
  return rc;
}

/*
................................................................................
    /* Read the %_segdir entry for index iIdx absolute level (iAbsLevel+1) */
    sqlite3_bind_int64(pSelect, 1, iAbsLevel+1);
    sqlite3_bind_int(pSelect, 2, iIdx);
    if( sqlite3_step(pSelect)==SQLITE_ROW ){
      iStart = sqlite3_column_int64(pSelect, 1);
      iLeafEnd = sqlite3_column_int64(pSelect, 2);
      fts3ReadEndBlockField(pSelect, 3, &iEnd, &pWriter->nLeafData);
      if( pWriter->nLeafData<0 ){
        pWriter->nLeafData = pWriter->nLeafData * -1;
      }
      nRoot = sqlite3_column_bytes(pSelect, 4);
      aRoot = sqlite3_column_blob(pSelect, 4);
    }else{
      return sqlite3_reset(pSelect);
    }

    /* Check for the zero-length marker in the %_segments table */
................................................................................
          if( nSeg!=0 ){
            bDirtyHint = 1;
            fts3IncrmergeHintPush(&hint, iAbsLevel, nSeg, &rc);
          }
        }
      }

      if( nSeg!=0 ){
        pWriter->nLeafData = pWriter->nLeafData * -1;
      }
      fts3IncrmergeRelease(p, pWriter, &rc);
      if( nSeg==0 ){
        fts3PromoteSegments(p, iAbsLevel+1, pWriter->nLeafData);
      }
    }

    sqlite3Fts3SegReaderFinish(pCsr);
................................................................................
*/
static int fts3DoAutoincrmerge(
  Fts3Table *p,                   /* FTS3 table handle */
  const char *zParam              /* Nul-terminated string containing boolean */
){
  int rc = SQLITE_OK;
  sqlite3_stmt *pStmt = 0;
  p->nAutoincrmerge = fts3Getint(&zParam);
  if( p->nAutoincrmerge==1 || p->nAutoincrmerge>FTS3_MERGE_COUNT ){
    p->nAutoincrmerge = 8;
  }
  if( !p->bHasStat ){
    assert( p->bFts4==0 );
    sqlite3Fts3CreateStatTable(&rc, p);
    if( rc ) return rc;
  }
  rc = fts3SqlStmt(p, SQL_REPLACE_STAT, &pStmt, 0);
  if( rc ) return rc;
  sqlite3_bind_int(pStmt, 1, FTS_STAT_AUTOINCRMERGE);
  sqlite3_bind_int(pStmt, 2, p->nAutoincrmerge);
  sqlite3_step(pStmt);
  rc = sqlite3_reset(pStmt);
  return rc;
}

/*
** Return a 64-bit checksum for the FTS index entry specified by the

Changes to test/fts4growth.test.

127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
...
231
232
233
234
235
236
237



238
239
240
241
242
243
244
...
252
253
254
255
256
257
258
259
260
261
262
263
264



265







266
267
268
269
270
271
272
273



































274
275
276
277
278
279
280
281
282







































283
284
285
286
287
288
289
290
291
292
293
294
295


296
297
298
299
300
301
302
303

do_execsql_test 2.5 { 
  SELECT end_block FROM x2_segdir WHERE level=3;
  INSERT INTO x2(x2) VALUES('merge=4,4');
  SELECT end_block FROM x2_segdir WHERE level=3;
  INSERT INTO x2(x2) VALUES('merge=4,4');
  SELECT end_block FROM x2_segdir WHERE level=3;
} {{3828 3430} {3828 10191} {3828 14109}}

do_execsql_test 2.6 {
  SELECT sum(length(block)) FROM x2_segdir, x2_segments WHERE 
    blockid BETWEEN start_block AND leaves_end_block
    AND level=3
} {14109}

................................................................................

do_test 3.3.2 {
  insert_doc 12
  execsql { SELECT level, idx, second(end_block) FROM x3_segdir WHERE level=1 }
} {1 0 412}

#--------------------------------------------------------------------------



do_execsql_test 4.1 {
  DROP TABLE IF EXISTS x4;
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(docid, words);
  CREATE VIRTUAL TABLE x4 USING fts4(words);
}
do_test 4.2 {
................................................................................
} {0 0 117483 0 1 118006}

do_execsql_test 4.4 {
  INSERT INTO x4(x4) VALUES('merge=10,2');
  SELECT count(*) FROM x4_segdir;
} {3}

breakpoint
do_execsql_test 4.5 {
  INSERT INTO x4(x4) VALUES('merge=10,2');
  SELECT count(*) FROM x4_segdir;
} {3}




if 0 {







do_execsql_test 3.1 {
  DROP TABLE IF EXISTS x2;
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(docid, words);
  CREATE VIRTUAL TABLE x2 USING fts4;
}
fts_kjv_genesis 




































proc t1_to_x2 {} {
  foreach id [db eval {SELECT docid FROM t1 LIMIT 2}] {
    execsql {
      DELETE FROM x2 WHERE docid=$id;
      INSERT INTO x2(docid, content) SELECT $id, words FROM t1 WHERE docid=$id;
    }
  }
}








































#do_test 3.2 {
  #t1_to_x2
  #execsql {SELECT level, count(*) FROM x2_segdir GROUP BY level}
#} {0 13 1 15 2 5}

proc second {x} { lindex $x 1 }
db func second second
for {set i 0} {$i <1000} {incr i} {
  t1_to_x2
  db eval {
    SELECT level, group_concat( second(end_block), ' ' ) AS c FROM x2_segdir GROUP BY level;
  } {
    puts "$i.$level: $c"


  }
}
}


finish_test









|







 







>
>
>







 







<





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







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









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





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


<
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
...
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
...
255
256
257
258
259
260
261

262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384




385
386


do_execsql_test 2.5 { 
  SELECT end_block FROM x2_segdir WHERE level=3;
  INSERT INTO x2(x2) VALUES('merge=4,4');
  SELECT end_block FROM x2_segdir WHERE level=3;
  INSERT INTO x2(x2) VALUES('merge=4,4');
  SELECT end_block FROM x2_segdir WHERE level=3;
} {{3828 -3430} {3828 -10191} {3828 -14109}}

do_execsql_test 2.6 {
  SELECT sum(length(block)) FROM x2_segdir, x2_segments WHERE 
    blockid BETWEEN start_block AND leaves_end_block
    AND level=3
} {14109}

................................................................................

do_test 3.3.2 {
  insert_doc 12
  execsql { SELECT level, idx, second(end_block) FROM x3_segdir WHERE level=1 }
} {1 0 412}

#--------------------------------------------------------------------------
# Check a theory on a bug in fts4 - that segments with idx==0 were not 
# being incrementally merged correctly. Theory turned out to be false.
#
do_execsql_test 4.1 {
  DROP TABLE IF EXISTS x4;
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(docid, words);
  CREATE VIRTUAL TABLE x4 USING fts4(words);
}
do_test 4.2 {
................................................................................
} {0 0 117483 0 1 118006}

do_execsql_test 4.4 {
  INSERT INTO x4(x4) VALUES('merge=10,2');
  SELECT count(*) FROM x4_segdir;
} {3}


do_execsql_test 4.5 {
  INSERT INTO x4(x4) VALUES('merge=10,2');
  SELECT count(*) FROM x4_segdir;
} {3}

do_execsql_test 4.6 {
  INSERT INTO x4(x4) VALUES('merge=1000,2');
  SELECT count(*) FROM x4_segdir;
} {1}



#--------------------------------------------------------------------------
# Check that segments are not promoted if the "end_block" field does not
# contain a size.
#
do_execsql_test 5.1 {
  DROP TABLE IF EXISTS x2;
  DROP TABLE IF EXISTS t1;
  CREATE TABLE t1(docid, words);
  CREATE VIRTUAL TABLE x2 USING fts4;
}
fts_kjv_genesis 

proc first {L} {lindex $L 0}
db func first first

do_test 5.2 {
  foreach r [db eval { SELECT rowid FROM t1 }] {
    execsql {
      INSERT INTO x2(docid, content) SELECT docid, words FROM t1 WHERE rowid=$r
    }
  }
  foreach d [db eval { SELECT docid FROM t1 LIMIT -1 OFFSET 20 }] {
    execsql { DELETE FROM x2 WHERE docid = $d }
  }

  execsql {
    INSERT INTO x2(x2) VALUES('optimize');
    SELECT level, idx, end_block FROM x2_segdir
  }
} {2 0 {752 1926}}

do_execsql_test 5.3 {
  UPDATE x2_segdir SET end_block = CAST( first(end_block) AS INTEGER );
  SELECT end_block, typeof(end_block) FROM x2_segdir;
} {752 integer}

do_execsql_test 5.4 {
  INSERT INTO x2 SELECT words FROM t1 LIMIT 50;
  SELECT level, idx, end_block FROM x2_segdir
} {2 0 752 0 0 {758 5174}}

do_execsql_test 5.5 {
  UPDATE x2_segdir SET end_block = end_block || ' 1926' WHERE level=2;
  INSERT INTO x2 SELECT words FROM t1 LIMIT 40;
  SELECT level, idx, end_block FROM x2_segdir
} {0 0 {752 1926} 0 1 {758 5174} 0 2 {763 4170}}

proc t1_to_x2 {} {
  foreach id [db eval {SELECT docid FROM t1 LIMIT 2}] {
    execsql {
      DELETE FROM x2 WHERE docid=$id;
      INSERT INTO x2(docid, content) SELECT $id, words FROM t1 WHERE docid=$id;
    }
  }
}

#--------------------------------------------------------------------------
# Check that segments created by auto-merge are not promoted until they
# are completed.
#

do_execsql_test 6.1 {
  CREATE VIRTUAL TABLE x5 USING fts4;
  INSERT INTO x5 SELECT words FROM t1 LIMIT 100 OFFSET 0;
  INSERT INTO x5 SELECT words FROM t1 LIMIT 100 OFFSET 25;
  INSERT INTO x5 SELECT words FROM t1 LIMIT 100 OFFSET 50;
  INSERT INTO x5 SELECT words FROM t1 LIMIT 100 OFFSET 75;
  SELECT count(*) FROM x5_segdir
} {4}

do_execsql_test 6.2 {
  INSERT INTO x5(x5) VALUES('merge=2,4');
  SELECT level, idx, end_block FROM x5_segdir;
} {0 0 {10 9216} 0 1 {21 9330} 0 2 {31 8850} 0 3 {40 8689} 1 0 {1320 -3117}}

do_execsql_test 6.3 {
  INSERT INTO x5 SELECT words FROM t1 LIMIT 100 OFFSET 100;
  SELECT level, idx, end_block FROM x5_segdir;
} {
  0 0 {10 9216} 0 1 {21 9330} 0 2 {31 8850} 
  0 3 {40 8689} 1 0 {1320 -3117} 0 4 {1329 8297}
}

do_execsql_test 6.4 {
  INSERT INTO x5(x5) VALUES('merge=200,4');
  SELECT level, idx, end_block FROM x5_segdir;
} {0 0 {1329 8297} 1 0 {1320 28009}}

do_execsql_test 6.5 {
  INSERT INTO x5 SELECT words FROM t1;
  SELECT level, idx, end_block FROM x5_segdir;
} {
  0 1 {1329 8297} 0 0 {1320 28009} 0 2 {1449 118006}
}

#do_test 3.2 {
  #t1_to_x2
  #execsql {SELECT level, count(*) FROM x2_segdir GROUP BY level}
#} {0 13 1 15 2 5}

#proc second {x} { lindex $x 1 }
#db func second second
#for {set i 0} {$i <1000} {incr i} {
#  t1_to_x2
#  db eval {
#    SELECT level, group_concat( second(end_block), ' ' ) AS c FROM x2_segdir GROUP BY level;
#  } {
#    puts "$i.$level: $c"
#  }
#}





finish_test


Added test/fts4growth2.test.



















































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
# 2014 May 12
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#*************************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this script is testing the FTS4 module.
#
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix fts4growth

# If SQLITE_ENABLE_FTS3 is defined, omit this file.
ifcapable !fts3 {
  finish_test
  return
}

source $testdir/genesis.tcl

do_execsql_test 1.0 { CREATE TABLE t1(docid, words); }
fts_kjv_genesis 

proc tt {val} {
  execsql {
    BEGIN;
      DELETE FROM x1 
        WHERE docid IN (SELECT docid FROM t1 WHERE (rowid-1)%4==$val+0);
      INSERT INTO x1(docid, content) 
        SELECT docid, words FROM t1 WHERE (rowid%4)==$val+0;
    COMMIT;
  }
}

do_execsql_test 1.1 {
  CREATE VIRTUAL TABLE x1 USING fts4;
  INSERT INTO x1(x1) VALUES('automerge=2');
}

do_test 1.2 {
  for {set i 0} {$i < 100} {incr i} {
    tt 0 ; tt 1 ; tt 2 ; tt 3
  }
  execsql { 
    SELECT max(level) FROM x1_segdir; 
    SELECT count(*) FROM x1_segdir WHERE level=4;
  }
} {4 1}

do_test 1.3 {
  for {set i 0} {$i < 100} {incr i} {
    tt 0 ; tt 1 ; tt 2 ; tt 3
  }
  execsql { 
    SELECT max(level) FROM x1_segdir; 
    SELECT count(*) FROM x1_segdir WHERE level=4;
  }
} {4 1}

#-------------------------------------------------------------------------
#
do_execsql_test 2.1 {
  DELETE FROM t1 WHERE rowid>16;
  DROP TABLE IF EXISTS x1;
  CREATE VIRTUAL TABLE x1 USING fts4;
}

db func second second
proc second {L} {lindex $L 1}

for {set tn 0} {$tn < 40} {incr tn} {
  do_test 2.2.$tn {
    for {set i 0} {$i < 100} {incr i} {
      tt 0 ; tt 1 ; tt 2 ; tt 3
    }
    execsql { SELECT max(level) FROM x1_segdir }
  } {1}
}


finish_test

Changes to test/permutations.test.

108
109
110
111
112
113
114

115
116
117
118
119
120
121
...
192
193
194
195
196
197
198

199
200
201
202
203
204
205
  savepoint4.test savepoint6.test select9.test 
  speed1.test speed1p.test speed2.test speed3.test speed4.test 
  speed4p.test sqllimits1.test tkt2686.test thread001.test thread002.test
  thread003.test thread004.test thread005.test trans2.test vacuum3.test 
  incrvacuum_ioerr.test autovacuum_crash.test btree8.test shared_err.test
  vtab_err.test walslow.test walcrash.test walcrash3.test
  walthread.test rtree3.test indexfault.test securedel2.test

}]
if {[info exists ::env(QUICKTEST_INCLUDE)]} {
  set allquicktests [concat $allquicktests $::env(QUICKTEST_INCLUDE)]
}

#############################################################################
# Start of tests
................................................................................
  fts3fault.test fts3malloc.test fts3matchinfo.test
  fts3aux1.test fts3comp1.test fts3auto.test
  fts4aa.test fts4content.test
  fts3conf.test fts3prefix.test fts3fault2.test fts3corrupt.test
  fts3corrupt2.test fts3first.test fts4langid.test fts4merge.test
  fts4check.test fts4unicode.test fts4noti.test
  fts3varint.test

}

test_suite "nofaultsim" -prefix "" -description {
  "Very" quick test suite. Runs in less than 5 minutes on a workstation. 
  This test suite is the same as the "quick" tests, except that some files
  that test malloc and IO errors are omitted.
} -files [







>







 







>







108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
...
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
  savepoint4.test savepoint6.test select9.test 
  speed1.test speed1p.test speed2.test speed3.test speed4.test 
  speed4p.test sqllimits1.test tkt2686.test thread001.test thread002.test
  thread003.test thread004.test thread005.test trans2.test vacuum3.test 
  incrvacuum_ioerr.test autovacuum_crash.test btree8.test shared_err.test
  vtab_err.test walslow.test walcrash.test walcrash3.test
  walthread.test rtree3.test indexfault.test securedel2.test
  fts3growth2.test
}]
if {[info exists ::env(QUICKTEST_INCLUDE)]} {
  set allquicktests [concat $allquicktests $::env(QUICKTEST_INCLUDE)]
}

#############################################################################
# Start of tests
................................................................................
  fts3fault.test fts3malloc.test fts3matchinfo.test
  fts3aux1.test fts3comp1.test fts3auto.test
  fts4aa.test fts4content.test
  fts3conf.test fts3prefix.test fts3fault2.test fts3corrupt.test
  fts3corrupt2.test fts3first.test fts4langid.test fts4merge.test
  fts4check.test fts4unicode.test fts4noti.test
  fts3varint.test
  fts4growth.test fts4growth2.test
}

test_suite "nofaultsim" -prefix "" -description {
  "Very" quick test suite. Runs in less than 5 minutes on a workstation. 
  This test suite is the same as the "quick" tests, except that some files
  that test malloc and IO errors are omitted.
} -files [