AryaWu/sqlite

1# 2016 March 82#3# The author disclaims copyright to this source code.  In place of4# 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#12 13set testdir [file dirname $argv0]14source $testdir/tester.tcl15source $testdir/fts3_common.tcl16set ::testprefix fts4opt17 18# If SQLITE_ENABLE_FTS3 is defined, omit this file.19ifcapable !fts3 {20  finish_test21  return22}23 24# Create the fts_kjv_genesis procedure which fills and FTS3/4 table 25# with the complete text of the Book of Genesis.26#27source $testdir/genesis.tcl28 29do_execsql_test 1.0 { CREATE TABLE t1(docid, words) }30fts_kjv_genesis31 32#-------------------------------------------------------------------------33# Argument $db is an open database handle. $tbl is the name of an FTS3/434# table with the database. This command rearranges the contents of the35# %_segdir table so that all segments within each index are on the same36# level. This means that the 'merge' command can then be used for an37# incremental optimize routine.38#39proc prepare_for_optimize {db tbl} {40  sqlite3_db_config $db DEFENSIVE 041  $db eval [string map [list % $tbl] {42    BEGIN;43      CREATE TEMP TABLE tmp_segdir(44        level, idx, start_block, leaves_end_block, end_block, root45      );46 47      INSERT INTO temp.tmp_segdir 48        SELECT 49        1024*(o.level / 1024) + 32,                                -- level50        sum(o.level<i.level OR (o.level=i.level AND o.idx>i.idx)), -- idx51        o.start_block, o.leaves_end_block, o.end_block, o.root     -- other52        FROM %_segdir o, %_segdir i 53        WHERE (o.level / 1024) = (i.level / 1024)54        GROUP BY o.level, o.idx;55  56      DELETE FROM %_segdir;57      INSERT INTO %_segdir SELECT * FROM temp.tmp_segdir;58      DROP TABLE temp.tmp_segdir;59  60    COMMIT;61  }]62}63 64do_test 1.1 {65  execsql { CREATE VIRTUAL TABLE t2 USING fts4(words, prefix="1,2,3") }66  foreach {docid words} [db eval { SELECT * FROM t1 }] {67    execsql { INSERT INTO t2(docid, words) VALUES($docid, $words) }68  }69} {}70 71do_execsql_test 1.2 {72  SELECT level, count(*) FROM t2_segdir GROUP BY level73} {74  0    13    1 15    2 5 75  1024 13 1025 15 1026 5 76  2048 13 2049 15 2050 5 77  3072 13 3073 15 3074 578}79 80do_execsql_test 1.3 { INSERT INTO t2(t2) VALUES('integrity-check') }81prepare_for_optimize db t282do_execsql_test 1.4 { INSERT INTO t2(t2) VALUES('integrity-check') }83 84do_execsql_test 1.5 {85  SELECT level, count(*) FROM t2_segdir GROUP BY level86} {87  32   33 88  1056 33 89  2080 33 90  3104 3391}92 93do_test 1.6 {94  while 1 {95    set tc1 [db total_changes]96    execsql { INSERT INTO t2(t2) VALUES('merge=5,2') }97    set tc2 [db total_changes]98    if {($tc2 - $tc1) < 2} break99  }100  execsql { SELECT level, count(*) FROM t2_segdir GROUP BY level }101} {33 1 1057 1 2081 1 3105 1}102do_execsql_test 1.7 { INSERT INTO t2(t2) VALUES('integrity-check') }103 104do_execsql_test 1.8 {105  INSERT INTO t2(words) SELECT words FROM t1;106  SELECT level, count(*) FROM t2_segdir GROUP BY level;107} {0 2 1024 2 2048 2 3072 2}108 109#-------------------------------------------------------------------------110 111do_execsql_test 2.0 {112  DELETE FROM t2;113}114do_test 2.1 {115  foreach {docid words} [db eval { SELECT * FROM t1 }] {116    execsql { INSERT INTO t2(docid, words) VALUES($docid, $words) }117  }118 119  set i 0120  foreach {docid words} [db eval { SELECT * FROM t1 }] {121    if {[incr i] % 2} { execsql { DELETE FROM t2 WHERE docid = $docid } }122  }123 124  set i 0125  foreach {docid words} [db eval { SELECT * FROM t1 }] {126    if {[incr i] % 3} {127      execsql { INSERT OR REPLACE INTO t2(docid, words) VALUES($docid, $words) }128    }129  }130} {}131 132do_execsql_test 2.2 {133  SELECT level, count(*) FROM t2_segdir GROUP BY level134} {135  0    10    1 15    2 12 136  1024 10 1025 15 1026 12 137  2048 10 2049 15 2050 12 138  3072 10 3073 15 3074 12139}140 141do_execsql_test 2.3 { INSERT INTO t2(t2) VALUES('integrity-check') }142prepare_for_optimize db t2143do_execsql_test 2.4 { INSERT INTO t2(t2) VALUES('integrity-check') }144 145do_execsql_test 2.5 {146  SELECT level, count(*) FROM t2_segdir GROUP BY level147} {148    32 37 149  1056 37 150  2080 37 151  3104 37152}153 154do_test 2.6 {155  while 1 {156    set tc1 [db total_changes]157    execsql { INSERT INTO t2(t2) VALUES('merge=5,2') }158    set tc2 [db total_changes]159    if {($tc2 - $tc1) < 2} break160  }161  execsql { SELECT level, count(*) FROM t2_segdir GROUP BY level }162} {33 1 1057 1 2081 1 3105 1}163do_execsql_test 2.7 { INSERT INTO t2(t2) VALUES('integrity-check') }164 165do_execsql_test 2.8 {166  INSERT INTO t2(words) SELECT words FROM t1;167  SELECT level, count(*) FROM t2_segdir GROUP BY level;168} {0 2 1024 2 2048 2 3072 2}169 170#-------------------------------------------------------------------------171# Check that 'optimize' works when there is data in the in-memory hash172# table, but no segments at all on disk.173#174do_execsql_test 3.1 {175  CREATE VIRTUAL TABLE fts USING fts4 (t);176  INSERT INTO fts (fts) VALUES ('optimize');177}178do_execsql_test 3.2 {179  INSERT INTO fts(fts) VALUES('integrity-check');180  SELECT count(*) FROM fts_segdir;181} {0}182do_execsql_test 3.3 {183  BEGIN;184  INSERT INTO fts (rowid, t) VALUES (2, 'test');185  INSERT INTO fts (fts) VALUES ('optimize');186  COMMIT;187  SELECT level, idx FROM fts_segdir;188} {0 0}189do_execsql_test 3.4 {190  INSERT INTO fts(fts) VALUES('integrity-check');191  SELECT rowid FROM fts WHERE fts MATCH 'test';192} {2}193do_execsql_test 3.5 {194  INSERT INTO fts (fts) VALUES ('optimize');195  INSERT INTO fts(fts) VALUES('integrity-check');196}197do_test 3.6 {198  set c1 [db total_changes]199  execsql { INSERT INTO fts (fts) VALUES ('optimize') }200  expr {[db total_changes] - $c1}201} {1}202do_test 3.7 {203  execsql { INSERT INTO fts (rowid, t) VALUES (3, 'xyz') }204  set c1 [db total_changes]205  execsql { INSERT INTO fts (fts) VALUES ('optimize') }206  expr {([db total_changes] - $c1) > 1}207} {1}208do_test 3.8 {209  set c1 [db total_changes]210  execsql { INSERT INTO fts (fts) VALUES ('optimize') }211  expr {[db total_changes] - $c1}212} {1}213 214finish_test215