Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Add a description of new fts5vocab table type "instance" to the the fts5 documentation. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
2ba9202168bd45857f5d4dc82e89b7f6 |
| User & Date: | dan 2017-08-12 16:44:52 |
Context
|
2017-08-25
| ||
| 16:28 | Update the speed and size graph. (check-in: a56a3b4391 user: drh tags: trunk) | |
|
2017-08-12
| ||
| 16:44 | Add a description of new fts5vocab table type "instance" to the the fts5 documentation. (check-in: 2ba9202168 user: dan tags: trunk) | |
|
2017-08-11
| ||
| 01:21 | Update the virtual table documentation to describe writable WITHOUT ROWID virtual tables. Also update the change log. (check-in: af4e59995a user: drh tags: trunk) | |
Changes
Changes to pages/fts5.in.
1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 .... 1600 1601 1602 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 .... 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 .... 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 |
<p> The fts5vocab virtual table module allows users to extract information from an FTS5 full-text index directly. The fts5vocab module is a part of FTS5 - it is available whenever FTS5 is. <p> Each fts5vocab table is associated with a single FTS5 table. An fts5vocab table is usually created by specifying two arguments in place of column names in the CREATE VIRTUAL TABLE statement - the name of the associated FTS5 table and the type of fts5vocab table. Currently there are two types of fts5vocab table, "row" and "col". Unless the fts5vocab table is created within the "temp" database, it must be part of the same database as the associated FTS5 table. <codeblock> <i>-- Create an fts5vocab "row" table to query the full-text index belonging -- to FTS5 table "ft1".</i> CREATE VIRTUAL TABLE ft1_v USING fts5vocab('ft1', 'row'); <i>-- Create an fts5vocab "col" table to query the full-text index belonging -- to FTS5 table "ft2".</i> CREATE VIRTUAL TABLE ft2_v USING fts5vocab(ft2, col); </codeblock> <p> If an fts5vocab table is created in the temp database, it may be associated with an FTS5 table in any attached database. In order to attach the fts5vocab table to an FTS5 table located in a database other than "temp", the name of the database is inserted before the FTS5 table name in the CREATE VIRTUAL TABLE arguments. For example: ................................................................................ <i>-- Create an fts5vocab "row" table to query the full-text index belonging -- to FTS5 table "ft1" in database "main".</i> CREATE VIRTUAL TABLE temp.ft1_v USING fts5vocab(main, 'ft1', 'row'); <i>-- Create an fts5vocab "col" table to query the full-text index belonging -- to FTS5 table "ft2" in attached database "aux".</i> CREATE VIRTUAL TABLE temp.ft2_v USING fts5vocab('aux', ft2, col); </codeblock> <p> Specifying three arguments when creating an fts5vocab table in any database other than "temp" results in an error. <p> An fts5vocab table of type "row" contains one row for each distinct term in the associated FTS5 table. The table columns are as follows: ................................................................................ <tr><td>col<td> The name of the FTS5 table column that contains the term. <tr><td>doc<td> The number of rows in the FTS5 table for which column $col contains at least one instance of the term. <tr><td>cnt<td> The total number of instances of the term that appear in column $col of the FTS5 table (considering all rows). </table> <p>Example: <codeblock> <i>-- Assuming a database created using:</i> CREATE VIRTUAL TABLE ft1 USING fts5(c1, c2); INSERT INTO ft1 VALUES('apple banana cherry', 'banana banana cherry'); INSERT INTO ft1 VALUES('cherry cherry cherry', 'date date date'); ................................................................................ -- -- apple | 1 | 1 -- banana | 1 | 3 -- cherry | 2 | 5 -- date | 1 | 3 --</i> CREATE VIRTUAL TABLE ft1_v_row USING fts5vocab(ft1, row); </codeblock> <h1 id=appendix_a nonumber tags="comparison with fts4"> Appendix A: Comparison with FTS3/4 </h1> |
| | | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > |
1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 .... 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 .... 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 .... 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 |
<p> The fts5vocab virtual table module allows users to extract information from an FTS5 full-text index directly. The fts5vocab module is a part of FTS5 - it is available whenever FTS5 is. <p> Each fts5vocab table is associated with a single FTS5 table. An fts5vocab table is usually created by specifying two arguments in place of column names in the CREATE VIRTUAL TABLE statement - the name of the associated FTS5 table and the type of fts5vocab table. Currently there are three types of fts5vocab table; "row", "col" and "instance". Unless the fts5vocab table is created within the "temp" database, it must be part of the same database as the associated FTS5 table. <codeblock> <i>-- Create an fts5vocab "row" table to query the full-text index belonging -- to FTS5 table "ft1".</i> CREATE VIRTUAL TABLE ft1_v USING fts5vocab('ft1', 'row'); <i>-- Create an fts5vocab "col" table to query the full-text index belonging -- to FTS5 table "ft2".</i> CREATE VIRTUAL TABLE ft2_v USING fts5vocab(ft2, col); <i>-- Create an fts5vocab "instance" table to query the full-text index -- belonging to FTS5 table "ft3".</i> CREATE VIRTUAL TABLE ft3_v USING fts5vocab(ft3, instance); </codeblock> <p> If an fts5vocab table is created in the temp database, it may be associated with an FTS5 table in any attached database. In order to attach the fts5vocab table to an FTS5 table located in a database other than "temp", the name of the database is inserted before the FTS5 table name in the CREATE VIRTUAL TABLE arguments. For example: ................................................................................ <i>-- Create an fts5vocab "row" table to query the full-text index belonging -- to FTS5 table "ft1" in database "main".</i> CREATE VIRTUAL TABLE temp.ft1_v USING fts5vocab(main, 'ft1', 'row'); <i>-- Create an fts5vocab "col" table to query the full-text index belonging -- to FTS5 table "ft2" in attached database "aux".</i> CREATE VIRTUAL TABLE temp.ft2_v USING fts5vocab('aux', ft2, col); <i>-- Create an fts5vocab "instance" table to query the full-text index -- belonging to FTS5 table "ft3" in attached database "other".</i> CREATE VIRTUAL TABLE temp.ft2_v USING fts5vocab('aux', ft3, 'instance'); </codeblock> <p> Specifying three arguments when creating an fts5vocab table in any database other than "temp" results in an error. <p> An fts5vocab table of type "row" contains one row for each distinct term in the associated FTS5 table. The table columns are as follows: ................................................................................ <tr><td>col<td> The name of the FTS5 table column that contains the term. <tr><td>doc<td> The number of rows in the FTS5 table for which column $col contains at least one instance of the term. <tr><td>cnt<td> The total number of instances of the term that appear in column $col of the FTS5 table (considering all rows). </table> <p> An fts5vocab table of type "instance" contains one row for each term instance stored in the associated FTS index. Assuming the FTS5 table is created with the 'detail' option set to 'full', table columns are as follows: <table striped=1> <tr><th>Column<th>Contents <tr><td>term<td> The term, as stored in the FTS5 index. <tr><td>doc<td> The rowid of the document that contains the term instance. <tr><td>col<td> The name of the column that contains the term instance. <tr><td>offset<td> The index of the term instance within its column. Terms are numbered in order of occurence starting from 0. </table> <p> If the FTS5 table is created with the 'detail' option set to 'col', then the <i>offset</i> column of an instance virtual table always contains NULL. In this case there is one row in the table for each unique term/doc/col combination. Or, if the FTS5 table is created with 'detail' set to 'none', then both <i>offset</i> and <i>col</i> always contain NULL values. For detail=none FTS5 tables, there is one row in the fts5vocab table for each unique term/doc combination. <p>Example: <codeblock> <i>-- Assuming a database created using:</i> CREATE VIRTUAL TABLE ft1 USING fts5(c1, c2); INSERT INTO ft1 VALUES('apple banana cherry', 'banana banana cherry'); INSERT INTO ft1 VALUES('cherry cherry cherry', 'date date date'); ................................................................................ -- -- apple | 1 | 1 -- banana | 1 | 3 -- cherry | 2 | 5 -- date | 1 | 3 --</i> CREATE VIRTUAL TABLE ft1_v_row USING fts5vocab(ft1, row); <i>-- And, for type "instance" INSERT INTO ft1 VALUES('apple banana cherry', 'banana banana cherry'); INSERT INTO ft1 VALUES('cherry cherry cherry', 'date date date'); -- -- apple | 1 | c1 | 0 -- banana | 1 | c1 | 1 -- banana | 1 | c2 | 0 -- banana | 1 | c2 | 1 -- cherry | 1 | c1 | 2 -- cherry | 1 | c2 | 2 -- cherry | 2 | c1 | 0 -- cherry | 2 | c1 | 1 -- cherry | 2 | c1 | 2 -- date | 2 | c2 | 0 -- date | 2 | c2 | 1 -- date | 2 | c2 | 2 --</i> CREATE VIRTUAL TABLE ft1_v_instance USING fts5vocab(ft1, instance); </codeblock> <h1 id=appendix_a nonumber tags="comparison with fts4"> Appendix A: Comparison with FTS3/4 </h1> |