Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add documentation for the columnsize=0 option to fts5.html. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
e30da647037b287c6b956e1a7349e196 |
User & Date: | dan 2015-06-10 13:01:48.483 |
Context
2015-06-10
| ||
16:28 | Update the "Differences from FTS3/4" section of fts5.html. (check-in: 22655b73bd user: dan tags: trunk) | |
13:01 | Add documentation for the columnsize=0 option to fts5.html. (check-in: e30da64703 user: dan tags: trunk) | |
2015-06-02
| ||
19:27 | Add syntax for multi-column matches to fts5.in. (check-in: ec116c4004 user: dan tags: trunk) | |
Changes
Changes to pages/fts5.in.
︙ | ︙ | |||
362 363 364 365 366 367 368 | <ul> <li> The "tokenize" option, used to configure a [FTS5 tokenizers | custom tokenizer]. <li> The "prefix" option, used to add [FTS5 prefix indexes | prefix indexes] to an FTS5 table. <li> The "content" option, used to make the FTS5 table an [FTS5 content option | external content or contentless table]. <li> The "content_rowid" option, used to set the rowid field of an | | > > > | 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 | <ul> <li> The "tokenize" option, used to configure a [FTS5 tokenizers | custom tokenizer]. <li> The "prefix" option, used to add [FTS5 prefix indexes | prefix indexes] to an FTS5 table. <li> The "content" option, used to make the FTS5 table an [FTS5 content option | external content or contentless table]. <li> The "content_rowid" option, used to set the rowid field of an [FTS5 external content tables | external content table]. <li> The [FTS5 columnsize option | "columnsize" option], used to configure whether or not the size in tokens of each value in the FTS5 table is stored separately within the database. </ul> <h2 tags="unindexed">The UNINDEXED column option</h2> <p>The contents of columns qualified with the UNINDEXED column option are not added to the FTS index. This means that for the purposes of MATCH queries and [FTS5 auxiliary functions], the column contains no matchable tokens. |
︙ | ︙ | |||
650 651 652 653 654 655 656 657 658 659 660 661 662 663 | END; CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c); INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c); END; </codeblock> <h1 tags="FTS5 auxiliary functions"> Auxiliary Functions </h1> <p> Auxiliary functions are similar to [corefunc | SQL scalar functions], except that they may only be used within full-text queries (those that use the MATCH operator) on an FTS5 table. Their results are calculated based not only on the arguments passed to them, but also on the current match and | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 | END; CREATE TRIGGER tbl_au AFTER UPDATE ON tbl BEGIN INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c); INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c); END; </codeblock> <h2 tags="FTS5 columnsize option">The Columnsize Option</h2> <p>Normally, FTS5 maintains a special backing table within the database that stores the size of each column value in tokens inserted into the main FTS5 table in a separate table. This backing table is used by the <a href=#xColumnSize>xColumnSize<a> API function, which is in turn used by the built-in [FTS5 bm25 | bm25 ranking function] (and is likely to be useful to other ranking functions as well). <p>In order to save space, this backing table may be omitted by setting the columnsize option to zero. For example: <codeblock> <i>-- A table without the xColumnSize() values stored on disk:</i> CREATE VIRTUAL TABLE ft USING fts5(a, b, c, columnsize=0); <i>-- Three equivalent ways of creating a table that does store the</i> <i>-- xColumnSize() values on disk:</i> CREATE VIRTUAL TABLE ft USING fts5(a, b, c); CREATE VIRTUAL TABLE ft USING fts5(a, b, c, columnsize=1); CREATE VIRTUAL TABLE ft USING fts5(a, b, columnsize='1', c); </codeblock> <p> It is an error to set the columnsize option to any value other than 0 or 1. <p> If an FTS5 table is configured with columnsize=0 but is not a [FTS5 contentless tables | contentless table], the xColumnSize API function still works, but runs much more slowly. In this case, instead of reading the value to return directly from the database, it reads the text value itself and count the tokens within it on demand. <p>Or, if the table is also a [FTS5 contentless tables | contentless table], then the following apply: <ul> <li> <p>The xColumnSize API always returns -1. There is no way to determine the number of tokens in a value stored within a contentless FTS5 table configured with columnsize=0. <li> <p>Each inserted row must be accompanied by an explicitly specified rowid value. If a contentless table is configured with columnsize=0, attempting to insert a NULL value into the rowid is an SQLITE_MISMATCH error. <li> <p>All queries on the table must use the MATCH operator. Attempting to SELECT data from the table without a MATCH operator in the WHERE clause is an error. </ul> <p> The name of the table in which the xColumnSize values are stored (unless columnsize=0 is specified) is "<name>_docsize", where <name> is the name of the FTS5 table itself. The <a href=https://www.sqlite.org/download.html>sqlite3_analyzer</a> tool may be used on an existing database in order to determine how much space might be saved by recreating an FTS5 table using columnsize=0. <h1 tags="FTS5 auxiliary functions"> Auxiliary Functions </h1> <p> Auxiliary functions are similar to [corefunc | SQL scalar functions], except that they may only be used within full-text queries (those that use the MATCH operator) on an FTS5 table. Their results are calculated based not only on the arguments passed to them, but also on the current match and |
︙ | ︙ |