SQLite

Check-in [e749be563d]
Login

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

Overview
Comment:Add the fts5 'optimize' command.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | fts5
Files: files | file ages | folders
SHA1: e749be563d8e738af113bd301770e2f22763ab77
User & Date: dan 2015-01-07 19:33:11.551
Context
2015-01-10
20:34
Fix some documentation issues in fts5. (check-in: 512e1bdb40 user: dan tags: fts5)
2015-01-07
19:33
Add the fts5 'optimize' command. (check-in: e749be563d user: dan tags: fts5)
17:11
Add the 'rebuild' and 'delete-all' commands. (check-in: 0cb2fed525 user: dan tags: fts5)
Changes
Unified Diff Ignore Whitespace Patch
Changes to ext/fts5/fts5.c.
1076
1077
1078
1079
1080
1081
1082


1083
1084
1085
1086
1087
1088
1089
      fts5SetVtabError(pTab, 
          "'rebuild' may not be used with a contentless fts5 table"
      );
      rc = SQLITE_ERROR;
    }else{
      rc = sqlite3Fts5StorageRebuild(pTab->pStorage);
    }


  }else if( 0==sqlite3_stricmp("integrity-check", z) ){
    rc = sqlite3Fts5StorageIntegrity(pTab->pStorage);
  }else{
    rc = sqlite3Fts5ConfigSetValue(pTab->pConfig, z, pVal, &bError);
    if( rc==SQLITE_OK ){
      if( bError ){
        rc = SQLITE_ERROR;







>
>







1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
      fts5SetVtabError(pTab, 
          "'rebuild' may not be used with a contentless fts5 table"
      );
      rc = SQLITE_ERROR;
    }else{
      rc = sqlite3Fts5StorageRebuild(pTab->pStorage);
    }
  }else if( 0==sqlite3_stricmp("optimize", z) ){
    rc = sqlite3Fts5StorageOptimize(pTab->pStorage);
  }else if( 0==sqlite3_stricmp("integrity-check", z) ){
    rc = sqlite3Fts5StorageIntegrity(pTab->pStorage);
  }else{
    rc = sqlite3Fts5ConfigSetValue(pTab->pConfig, z, pVal, &bError);
    if( rc==SQLITE_OK ){
      if( bError ){
        rc = SQLITE_ERROR;
Changes to ext/fts5/fts5Int.h.
337
338
339
340
341
342
343

344
345
346
347
348
349
350
/*
** Return the total number of entries read from the %_data table by 
** this connection since it was created.
*/
int sqlite3Fts5IndexReads(Fts5Index *p);

int sqlite3Fts5IndexReinit(Fts5Index *p);


/*
** End of interface to code in fts5_index.c.
**************************************************************************/

/**************************************************************************
** Interface to code in fts5_hash.c. 







>







337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
/*
** Return the total number of entries read from the %_data table by 
** this connection since it was created.
*/
int sqlite3Fts5IndexReads(Fts5Index *p);

int sqlite3Fts5IndexReinit(Fts5Index *p);
int sqlite3Fts5IndexOptimize(Fts5Index *p);

/*
** End of interface to code in fts5_index.c.
**************************************************************************/

/**************************************************************************
** Interface to code in fts5_hash.c. 
421
422
423
424
425
426
427

428
429
430
431
432
433
434

int sqlite3Fts5StorageConfigValue(Fts5Storage *p, const char*, sqlite3_value*);

int sqlite3Fts5StorageSpecialDelete(Fts5Storage *p, i64 iDel, sqlite3_value**);

int sqlite3Fts5StorageDeleteAll(Fts5Storage *p);
int sqlite3Fts5StorageRebuild(Fts5Storage *p);


/*
** End of interface to code in fts5_storage.c.
**************************************************************************/


/**************************************************************************







>







422
423
424
425
426
427
428
429
430
431
432
433
434
435
436

int sqlite3Fts5StorageConfigValue(Fts5Storage *p, const char*, sqlite3_value*);

int sqlite3Fts5StorageSpecialDelete(Fts5Storage *p, i64 iDel, sqlite3_value**);

int sqlite3Fts5StorageDeleteAll(Fts5Storage *p);
int sqlite3Fts5StorageRebuild(Fts5Storage *p);
int sqlite3Fts5StorageOptimize(Fts5Storage *p);

/*
** End of interface to code in fts5_storage.c.
**************************************************************************/


/**************************************************************************
Changes to ext/fts5/fts5_index.c.
37
38
39
40
41
42
43

44
45
46
47
48
49
50
**     large doclists with very small doclists.
**
**   * extra fields in the "structure record" record the state of ongoing
**     incremental merge operations.
**
*/


#define FTS5_WORK_UNIT      64    /* Number of leaf pages in unit of work */
#define FTS5_CRISIS_MERGE   16    /* Maximum number of segments to merge */

#define FTS5_MIN_DLIDX_SIZE  4    /* Add dlidx if this many empty pages */

/*
** Details:







>







37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
**     large doclists with very small doclists.
**
**   * extra fields in the "structure record" record the state of ongoing
**     incremental merge operations.
**
*/

#define FTS5_OPT_WORK_UNIT  1000  /* Number of leaf pages per optimize step */
#define FTS5_WORK_UNIT      64    /* Number of leaf pages in unit of work */
#define FTS5_CRISIS_MERGE   16    /* Maximum number of segments to merge */

#define FTS5_MIN_DLIDX_SIZE  4    /* Add dlidx if this many empty pages */

/*
** Details:
3159
3160
3161
3162
3163
3164
3165






3166
3167
3168
3169
3170
3171
3172
  ){
    fts5IndexMergeLevel(p, iIdx, &pStruct, iLvl, 0);
    fts5StructurePromote(p, iLvl+1, pStruct);
    iLvl++;
  }
  *ppStruct = pStruct;
}







typedef struct Fts5FlushCtx Fts5FlushCtx;
struct Fts5FlushCtx {
  Fts5Index *pIdx;
  Fts5SegWriter writer; 
};








>
>
>
>
>
>







3160
3161
3162
3163
3164
3165
3166
3167
3168
3169
3170
3171
3172
3173
3174
3175
3176
3177
3178
3179
  ){
    fts5IndexMergeLevel(p, iIdx, &pStruct, iLvl, 0);
    fts5StructurePromote(p, iLvl+1, pStruct);
    iLvl++;
  }
  *ppStruct = pStruct;
}

static int fts5IndexReturn(Fts5Index *p){
  int rc = p->rc;
  p->rc = SQLITE_OK;
  return rc;
}

typedef struct Fts5FlushCtx Fts5FlushCtx;
struct Fts5FlushCtx {
  Fts5Index *pIdx;
  Fts5SegWriter writer; 
};

3272
3273
3274
3275
3276
3277
3278




























































3279
3280
3281
3282
3283
3284
3285

  /* Flush the terms and each prefix index to disk */
  for(i=0; i<=pConfig->nPrefix; i++){
    fts5FlushOneHash(p, i, &nLeaf);
  }
  p->nPendingData = 0;
}





























































/*
** Return a simple checksum value based on the arguments.
*/
static u64 fts5IndexEntryCksum(
  i64 iRowid, 
  int iCol, 







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







3279
3280
3281
3282
3283
3284
3285
3286
3287
3288
3289
3290
3291
3292
3293
3294
3295
3296
3297
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
3324
3325
3326
3327
3328
3329
3330
3331
3332
3333
3334
3335
3336
3337
3338
3339
3340
3341
3342
3343
3344
3345
3346
3347
3348
3349
3350
3351
3352

  /* Flush the terms and each prefix index to disk */
  for(i=0; i<=pConfig->nPrefix; i++){
    fts5FlushOneHash(p, i, &nLeaf);
  }
  p->nPendingData = 0;
}


int sqlite3Fts5IndexOptimize(Fts5Index *p){
  Fts5Config *pConfig = p->pConfig;
  int i;

  fts5IndexFlush(p);
  for(i=0; i<=pConfig->nPrefix; i++){
    Fts5Structure *pStruct = fts5StructureRead(p, i);
    Fts5Structure *pNew = 0;
    int nSeg = 0;
    if( pStruct ){
      nSeg = fts5StructureCountSegments(pStruct);
      if( nSeg>1 ){
        int nByte = sizeof(Fts5Structure);
        nByte += (pStruct->nLevel+1) * sizeof(Fts5StructureLevel);
        pNew = (Fts5Structure*)sqlite3Fts5MallocZero(&p->rc, nByte);
      }
    }
    if( pNew ){
      Fts5StructureLevel *pLvl;
      int nByte = nSeg * sizeof(Fts5StructureSegment);
      pNew->nLevel = pStruct->nLevel+1;
      pNew->nWriteCounter = pStruct->nWriteCounter;
      pLvl = &pNew->aLevel[pStruct->nLevel];
      pLvl->aSeg = (Fts5StructureSegment*)sqlite3Fts5MallocZero(&p->rc, nByte);
      if( pLvl->aSeg ){
        int iLvl, iSeg;
        int iSegOut = 0;
        for(iLvl=0; iLvl<pStruct->nLevel; iLvl++){
          for(iSeg=0; iSeg<pStruct->aLevel[iLvl].nSeg; iSeg++){
            pLvl->aSeg[iSegOut] = pStruct->aLevel[iLvl].aSeg[iSeg];
            iSegOut++;
          }
        }
        pLvl->nSeg = nSeg;
      }else{
        sqlite3_free(pNew);
        pNew = 0;
      }
    }

    if( pNew ){
      int iLvl = pNew->nLevel-1;
      while( p->rc==SQLITE_OK && pNew->aLevel[iLvl].nSeg>0 ){
        int nRem = FTS5_OPT_WORK_UNIT;
        fts5IndexMergeLevel(p, i, &pNew, iLvl, &nRem);
      }

      fts5StructureWrite(p, i, pNew);
      fts5StructureRelease(pNew);
    }

    fts5StructureRelease(pStruct);
  }

  return fts5IndexReturn(p); 
}



/*
** Return a simple checksum value based on the arguments.
*/
static u64 fts5IndexEntryCksum(
  i64 iRowid, 
  int iCol, 
3784
3785
3786
3787
3788
3789
3790
3791
3792
3793
3794
3795
3796
3797
3798
3799
3800
3801
3802
3803
    }
  }

  fts5StructureRelease(pStruct);
  sqlite3_free(aBuf);
}

static int fts5IndexReturn(Fts5Index *p){
  int rc = p->rc;
  p->rc = SQLITE_OK;
  return rc;
}

/*
** Run internal checks to ensure that the FTS index (a) is internally 
** consistent and (b) contains entries for which the XOR of the checksums
** as calculated by fts5IndexEntryCksum() is cksum.
**
** Return SQLITE_CORRUPT if any of the internal checks fail, or if the
** checksum does not match. Return SQLITE_OK if all checks pass without







<
<
<
<
<
<







3851
3852
3853
3854
3855
3856
3857






3858
3859
3860
3861
3862
3863
3864
    }
  }

  fts5StructureRelease(pStruct);
  sqlite3_free(aBuf);
}







/*
** Run internal checks to ensure that the FTS index (a) is internally 
** consistent and (b) contains entries for which the XOR of the checksums
** as calculated by fts5IndexEntryCksum() is cksum.
**
** Return SQLITE_CORRUPT if any of the internal checks fail, or if the
** checksum does not match. Return SQLITE_OK if all checks pass without
Changes to ext/fts5/fts5_storage.c.
580
581
582
583
584
585
586




587
588
589
590
591
592
593

  /* Write the averages record */
  if( rc==SQLITE_OK ){
    rc = fts5StorageSaveTotals(p);
  }
  return rc;
}





/*
** Allocate a new rowid. This is used for "external content" tables when
** a NULL value is inserted into the rowid column. The new rowid is allocated
** by inserting a dummy row into the %_docsize table. The dummy will be
** overwritten later.
*/







>
>
>
>







580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597

  /* Write the averages record */
  if( rc==SQLITE_OK ){
    rc = fts5StorageSaveTotals(p);
  }
  return rc;
}

int sqlite3Fts5StorageOptimize(Fts5Storage *p){
  return sqlite3Fts5IndexOptimize(p->pIndex);
}

/*
** Allocate a new rowid. This is used for "external content" tables when
** a NULL value is inserted into the rowid column. The new rowid is allocated
** by inserting a dummy row into the %_docsize table. The dummy will be
** overwritten later.
*/
Added ext/fts5/test/fts5optimize.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
# 2014 Dec 20
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
#
#

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

proc rnddoc {nWord} {
  set vocab {a b c d e f g h i j k l m n o p q r s t u v w x y z}
  set nVocab [llength $vocab]
  set ret [list]
  for {set i 0} {$i < $nWord} {incr i} {
    lappend ret [lindex $vocab [expr {int(rand() * $nVocab)}]]
  }
  return $ret
}


foreach {tn nStep} {
  1 2
  2 10
  3 50
  4 500
} {
if {$tn!=4} continue
  reset_db
  db func rnddoc rnddoc
  do_execsql_test 1.$tn.1 {
    CREATE VIRTUAL TABLE t1 USING fts5(x, y);
  }
  do_test 1.$tn.2 {
    for {set i 0} {$i < $nStep} {incr i} {
      execsql { INSERT INTO t1 VALUES( rnddoc(5), rnddoc(5) ) }
    }
  } {}

  do_execsql_test 1.$tn.3 {
    INSERT INTO t1(t1) VALUES('integrity-check');
  }

  do_execsql_test 1.$tn.4 {
    INSERT INTO t1(t1) VALUES('optimize');
  }

  do_execsql_test 1.$tn.5 {
    INSERT INTO t1(t1) VALUES('integrity-check');
  }
}

finish_test