SQLite

/* 27 */ "SELECT ? UNION SELECT level / (1024 * ?) FROM %Q.'%q_segdir'",

/* This statement is used to determine which level to read the input from
** when performing an incremental merge. It returns the absolute level number
** of the oldest level in the db that contains at least ? segments. Or,
** if no level in the FTS index contains more than ? segments, the statement
** returns zero rows.  */
/* 28 */ "SELECT level, count(*) AS cnt FROM %Q.'%q_segdir' "
         "  GROUP BY level HAVING cnt>=?"
         "  ORDER BY (level %% 1024) ASC LIMIT 1",

/* Estimate the upper limit on the number of leaf nodes in a new segment
** created by merging the oldest :2 segments from absolute level :1. See 
** function sqlite3Fts3Incrmerge() for details.  */
/* 29 */ "SELECT 2 * total(1 + leaves_end_block - start_block) "
         "  FROM %Q.'%q_segdir' WHERE level = ? AND idx < ?",

    /* Search the %_segdir table for the absolute level with the smallest
    ** relative level number that contains at least nMin segments, if any.
    ** If one is found, set iAbsLevel to the absolute level number and
    ** nSeg to nMin. If no level with at least nMin segments can be found, 
    ** set nSeg to -1.
    */
    rc = fts3SqlStmt(p, SQL_FIND_MERGE_LEVEL, &pFindLevel, 0);
    sqlite3_bind_int(pFindLevel, 1, MAX(2, nMin));
    if( sqlite3_step(pFindLevel)==SQLITE_ROW ){
      iAbsLevel = sqlite3_column_int64(pFindLevel, 0);
      if( nMin<2 ){
        nSeg = sqlite3_column_int(pFindLevel, 1);
      }else{
        nSeg = nMin;
      }
    }else{
      nSeg = -1;
    }
    rc = sqlite3_reset(pFindLevel);

    /* If the hint read from the %_stat table is not empty, check if the
    ** last entry in it specifies a relative level smaller than or equal

  /* If the first integer value is followed by a ',',  read the second
  ** integer value. */
  if( z[0]==',' && z[1]!='\0' ){
    z++;
    nMin = fts3Getint(&z);
  }

  if( z[0]!='\0' || nMin<0 || nMin==1 ){
    rc = SQLITE_ERROR;
  }else{
    rc = SQLITE_OK;
    if( !p->bHasStat ){
      assert( p->bFts4==0 );
      sqlite3Fts3CreateStatTable(&rc, p);
    }

    1   {merge=abc}
    2   {merge=%%%}
    3   {merge=,}
    4   {merge=5,}
    5   {merge=6,%}
    6   {merge=6,six}
    7   {merge=6,1}

  } {
    do_catchsql_test 2.$tn { 
      INSERT INTO t2(t2) VALUES($arg);
    } {1 {SQL logic error or missing database}}
  }
  
  #-------------------------------------------------------------------------

# 2016 March 8
#
# 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.
#
#*************************************************************************
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
source $testdir/fts3_common.tcl
set ::testprefix fts4opt

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

# Create the fts_kjv_genesis procedure which fills and FTS3/4 table 
# with the complete text of the Book of Genesis.
#
source $testdir/genesis.tcl

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

#-------------------------------------------------------------------------
# Argument $db is an open database handle. $tbl is the name of an FTS3/4
# table with the database. This command rearranges the contents of the
# %_segdir table so that all segments within each index are on the same
# level. This means that the 'merge' command can then be used for an
# incremental optimize routine.
#
proc prepare_for_optimize {db tbl} {
  $db eval [string map [list % $tbl] {
    BEGIN;
      CREATE TEMP TABLE tmp_segdir(
        level, idx, start_block, leaves_end_block, end_block, root
      );

      INSERT INTO temp.tmp_segdir 
        SELECT 
        1024*(o.level / 1024) + 32,                                -- level
        sum(o.level<i.level OR (o.level=i.level AND o.idx>i.idx)), -- idx
        o.start_block, o.leaves_end_block, o.end_block, o.root     -- other
        FROM %_segdir o, %_segdir i 
        WHERE (o.level / 1024) = (i.level / 1024)
        GROUP BY o.level, o.idx;
  
      DELETE FROM %_segdir;
      INSERT INTO %_segdir SELECT * FROM temp.tmp_segdir;
      DROP TABLE temp.tmp_segdir;
  
    COMMIT;
  }]
}

do_test 1.1 {
  execsql { CREATE VIRTUAL TABLE t2 USING fts4(words, prefix="1,2,3") }
  foreach {docid words} [db eval { SELECT * FROM t1 }] {
    execsql { INSERT INTO t2(docid, words) VALUES($docid, $words) }
  }
} {}

do_execsql_test 1.2 {
  SELECT level, count(*) FROM t2_segdir GROUP BY level
} {
  0    13    1 15    2 5 
  1024 13 1025 15 1026 5 
  2048 13 2049 15 2050 5 
  3072 13 3073 15 3074 5
}

do_execsql_test 1.3 { INSERT INTO t2(t2) VALUES('integrity-check') }
prepare_for_optimize db t2
do_execsql_test 1.4 { INSERT INTO t2(t2) VALUES('integrity-check') }

do_execsql_test 1.5 {
  SELECT level, count(*) FROM t2_segdir GROUP BY level
} {
  32   33 
  1056 33 
  2080 33 
  3104 33
}

do_test 1.6 {
  while 1 {
    set tc1 [db total_changes]
    execsql { INSERT INTO t2(t2) VALUES('merge=5,0') }
    set tc2 [db total_changes]
    if {($tc2 - $tc1) < 2} break
  }
  execsql { SELECT level, count(*) FROM t2_segdir GROUP BY level }
} {33 1 1057 1 2081 1 3105 1}
do_execsql_test 1.7 { INSERT INTO t2(t2) VALUES('integrity-check') }

do_execsql_test 1.8 {
  INSERT INTO t2(words) SELECT words FROM t1;
  SELECT level, count(*) FROM t2_segdir GROUP BY level;
} {0 2 1024 2 2048 2 3072 2}

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

do_execsql_test 2.0 {
  DELETE FROM t2;
}
do_test 2.1 {
  foreach {docid words} [db eval { SELECT * FROM t1 }] {
    execsql { INSERT INTO t2(docid, words) VALUES($docid, $words) }
  }

  set i 0
  foreach {docid words} [db eval { SELECT * FROM t1 }] {
    if {[incr i] % 2} { execsql { DELETE FROM t2 WHERE docid = $docid } }
  }

  set i 0
  foreach {docid words} [db eval { SELECT * FROM t1 }] {
    if {[incr i] % 3} {
      execsql { INSERT OR REPLACE INTO t2(docid, words) VALUES($docid, $words) }
    }
  }
} {}

do_execsql_test 2.2 {
  SELECT level, count(*) FROM t2_segdir GROUP BY level
} {
  0    10    1 15    2 12 
  1024 10 1025 15 1026 12 
  2048 10 2049 15 2050 12 
  3072 10 3073 15 3074 12
}

do_execsql_test 2.3 { INSERT INTO t2(t2) VALUES('integrity-check') }
prepare_for_optimize db t2
do_execsql_test 2.4 { INSERT INTO t2(t2) VALUES('integrity-check') }

do_execsql_test 2.5 {
  SELECT level, count(*) FROM t2_segdir GROUP BY level
} {
    32 37 
  1056 37 
  2080 37 
  3104 37
}

do_test 2.6 {
  while 1 {
    set tc1 [db total_changes]
    execsql { INSERT INTO t2(t2) VALUES('merge=5,0') }
    set tc2 [db total_changes]
    if {($tc2 - $tc1) < 2} break
  }
  execsql { SELECT level, count(*) FROM t2_segdir GROUP BY level }
} {33 1 1057 1 2081 1 3105 1}
do_execsql_test 2.7 { INSERT INTO t2(t2) VALUES('integrity-check') }

do_execsql_test 2.8 {
  INSERT INTO t2(words) SELECT words FROM t1;
  SELECT level, count(*) FROM t2_segdir GROUP BY level;
} {0 2 1024 2 2048 2 3072 2}

finish_test