Documentation Source Text

Check-in [e68ac068e8]
Login

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

Overview
Comment:Add a new example for external-content tables to fts3.html.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: e68ac068e86dcee12c6ec1510513e1c9a5a2b527
User & Date: dan 2015-10-10 09:36:04
Context
2015-10-12
04:46
Fix typos in new documents. check-in: 110e2bb5b0 user: drh tags: trunk
2015-10-10
09:36
Add a new example for external-content tables to fts3.html. check-in: e68ac068e8 user: dan tags: trunk
2015-10-09
15:46
Add a report of the SEE corruption bug to the 3.9.0 release notes. check-in: 8b38705a3d user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/fts3.in.

1722
1723
1724
1725
1726
1727
1728

























1729
1730
1731
1732
1733
1734
1735
  deleted. If the content table row cannot be found, or if it contains values
  inconsistent with the contents of the FTS index, the results can be difficult
  to predict. The FTS index may be left containing entries corresponding to the
  deleted row, which can lead to seemingly nonsensical results being returned
  by subsequent SELECT queries. The same applies when a row is updated, as
  internally an UPDATE is the same as a DELETE followed by an INSERT.


























<p>  
  Instead of writing separately to the full-text index and the content table,
  some users may wish to use database triggers to keep the full-text index
  up to date with respect to the set of documents stored in the content table.
  For example, using the tables from earlier examples:

<codeblock>







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







1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
  deleted. If the content table row cannot be found, or if it contains values
  inconsistent with the contents of the FTS index, the results can be difficult
  to predict. The FTS index may be left containing entries corresponding to the
  deleted row, which can lead to seemingly nonsensical results being returned
  by subsequent SELECT queries. The same applies when a row is updated, as
  internally an UPDATE is the same as a DELETE followed by an INSERT.

<p>
  This means that in order to keep an FTS in sync with an external content
  table, any UPDATE or DELETE operations must be applied first to the FTS
  table, and then to the external content table. For example:

<codeblock>
  CREATE TABLE t1_real(id INTEGER PRIMARY KEY, a, b, c, d);
  CREATE VIRTUAL TABLE t1_fts USING fts4(content="t1_real", b, c);

  <i>-- This works. When the row is removed from the FTS table, FTS retrieves 
  -- the row with rowid=123 and tokenizes it in order to determine the entries 
  -- that must be removed from the full-text index.
  --</i> 
  DELETE FROM t1_fts WHERE rowid = 123;
  DELETE FROM t1_real WHERE rowid = 123;

  --<i> This <b>does not work</b>. By the time the FTS table is updated, the row
  -- has already been deleted from the underlying content table. As a result
  -- FTS is unable to determine the entries to remove from the FTS index and
  -- so the index and content table are left out of sync.
  --</i>
  DELETE FROM t1_real WHERE rowid = 123;
  DELETE FROM t1_fts WHERE rowid = 123;
</codeblock>

<p>  
  Instead of writing separately to the full-text index and the content table,
  some users may wish to use database triggers to keep the full-text index
  up to date with respect to the set of documents stored in the content table.
  For example, using the tables from earlier examples:

<codeblock>