Documentation Source Text

Check-in [9ed173645f]
Login

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

Overview
Comment:Update the fts5 documentation for the 'usermerge' option and 'merge' command.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 9ed173645f16a46d8c1733e27fa975c0bdcb9411
User & Date: dan 2016-03-09 20:54:46
Context
2016-03-10
18:52
Add extra documentation for fts5 incremental optimize. check-in: b4fa1baa79 user: dan tags: trunk
2016-03-09
20:54
Update the fts5 documentation for the 'usermerge' option and 'merge' command. check-in: 9ed173645f user: dan tags: trunk
16:03
Another bullet on the 3.12.0 change log. check-in: 1b4e717575 user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/fts5.in.

1205
1206
1207
1208
1209
1210
1211
1212

1213
1214
1215
1216
1217

































1218
1219
1220
1221
1222
1223
1224
....
1264
1265
1266
1267
1268
1269
1270














1271
1272
1273
1274
1275
1276
1277
<h2 tags="FTS5 merge command">The 'merge' Command</h2>

<codeblock>
  INSERT INTO ft(ft, rank) VALUES('merge', 500);
</codeblock>

<p> This command merges b-tree structures together until roughly N pages
of merged data have been written to the database. The size of each page is

as configured by the [FTS5 pgsz option].

<p> B-tree structures are only eligible for merging if they would also be
eligible for automatic merging as configured by the [FTS5 automerge option].
If no such b-trees are found when this command is invoked, it is a no-op.


































<h2 tags="FTS5 optimize command">The 'optimize' Command</h2>

<p>This command merges all individual b-trees that currently make up the
full-text index into a single large b-tree structure. This ensures that the
full-text index consumes the minimum space within the database and is in the
fastest form to query.
................................................................................
table].  It is not available with [FTS5 contentless tables | contentless
tables].

<codeblock>
  INSERT INTO ft(ft) VALUES('rebuild');
</codeblock>
















<h1 tags="Extending FTS5">Extending FTS5</h1>

<p>FTS5 features APIs allowing it to be extended by:

<ul>
  <li> Adding new auxiliary functions implemented in C, and







|
>
|

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







 







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







1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216

1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
....
1297
1298
1299
1300
1301
1302
1303
1304
1305
1306
1307
1308
1309
1310
1311
1312
1313
1314
1315
1316
1317
1318
1319
1320
1321
1322
1323
1324
<h2 tags="FTS5 merge command">The 'merge' Command</h2>

<codeblock>
  INSERT INTO ft(ft, rank) VALUES('merge', 500);
</codeblock>

<p> This command merges b-tree structures together until roughly N pages
of merged data have been written to the database, where N is the absolute
value of the parameter specified as part of the 'merge' command. The size of
each page is as configured by the [FTS5 pgsz option].

<p> If the parameter is a postive value, B-tree structures are only eligible

for merging if one of the following is true:

<ul>
  <li> There are [FTS5 usermerge option | usermerge] or more such b-trees on a
       single level (see the documentation for the [FTS5 automerge option]
       for an explanation of b-tree levels).
  <li> A merge has already been started (perhaps by a 'merge' command that
       specified a negative parameter).
</ul>

<p> It is possible to tell whether or not the 'merge' command found any 
b-trees to merge together by checking the value returned by the
[sqlite3_total_changes()] API before and after the command is executed. If
the difference between the two values is 2 or greater, then work was performed.
If the difference is less than 2, then the 'merge' command was a no-op. In this
case there is no reason to execute the same 'merge' command again, at least
until the FTS table is updated again.

<p> If the parameter is negative, and there are B-tree structures on more than
one level within the FTS index, all B-tree structures are assigned to the same
level before the merge operation is commenced. Additionally, if the parameter
is negative, the value of the usermerge configuration option is not 
respected - as few as two b-trees from the same level may be merged together.

<p> The above means that executing the 'merge' command with a negative
parameter until the before and after difference in the return value of
[sqlite3_total_changes()] is less than two optimizes the FTS index in the
same way as the [FTS5 optimize command]. However, if a new b-tree is added
to the FTS index while this process is ongoing, FTS5 will move the new 
b-tree to the same level as the existing b-trees and restart the merge. To
avoid this, only the first call to 'merge' should specify a negative parameter.
Each subsequent call to 'merge' should specify a postive value so that the
merge started by the first call is run to completion even if new b-trees are
added to the FTS index.

<h2 tags="FTS5 optimize command">The 'optimize' Command</h2>

<p>This command merges all individual b-trees that currently make up the
full-text index into a single large b-tree structure. This ensures that the
full-text index consumes the minimum space within the database and is in the
fastest form to query.
................................................................................
table].  It is not available with [FTS5 contentless tables | contentless
tables].

<codeblock>
  INSERT INTO ft(ft) VALUES('rebuild');
</codeblock>

<h2 tags="FTS5 usermerge option">The 'usermerge' Configuration Option</h2>

<p> This command is used to set the persistent "usermerge" option.

<p> The usermerge option is similar to the automerge and crisismerge options.
It is the minimum number of b-tree segments that will be merged together by
a 'merge' command with a positive parameter. For example:

<codeblock>
  INSERT INTO ft(ft, rank) VALUES('usermerge', 4);
</codeblock>

<p> The default value of the usermerge option is 4. The minimum allowed value
is 2, and the maximum 16.

<h1 tags="Extending FTS5">Extending FTS5</h1>

<p>FTS5 features APIs allowing it to be extended by:

<ul>
  <li> Adding new auxiliary functions implemented in C, and