Documentation Source Text

Check-in [e30da64703]
Login

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: e30da647037b287c6b956e1a7349e1968abd4971
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
Unified Diff Ignore Whitespace Patch
Changes to pages/fts5.in.
362
363
364
365
366
367
368
369



370
371
372
373
374
375
376
<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].



</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. 







|
>
>
>







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 "&lt;name&gt;_docsize", where 
&lt;name&gt; 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