StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUsing SQLite FTS3 with INTEGER columns
text
Body
copied!<p>I'd like to use SQLite FTS3 (FTS4, actually) to index a table with integer columns, conceptually something like this:</p> <pre><code>CREATE VIRTUAL TABLE whole (document INTEGER, page INTEGER, content TEXT, UNIQUE(document, page)) USING fts4(); </code></pre> <p>I know that FTS3 treats all columns other than rowid as TEXT, so I'll have to use two tables:</p> <pre><code>CREATE VIRTUAL TABLE data USING fts4(); CREATE TABLE metadata(document INTEGER, page INTEGER, UNIQUE(document, page)); </code></pre> <p>I want to be able to query for documents, or for pages in a given document:</p> <pre><code>SELECT DISTINCT document FROM metadata NATURAL JOIN data WHERE content MATCH 'foo'; SELECT page FROM metadata NATURAL JOIN data WHERE document = 123 AND content MATCH 'foo'; </code></pre> <p>I think the NATURAL JOIN requires me to ensure that the rowids are kept in sync, but what's the best way to do that? Should I be using a FOREIGN KEY or other constraint? Would a sub-select be better than a join?</p> <p>I'd like an insertion for a document and page already in the database to overwrite the text content. Is that possible programmatically via SQL or will I have to check to see if the row already exists in the info table?</p> <p>I'm also going to be wanting to DELETE FROM both tables for a given document -- is there a way to do this in a single statement?</p> <p>All advice gratefully received, but as I am a SQL newbie, code samples particularly appreciated!</p> <p><strong>Update:</strong> It's not at all clear to me how I can create a foreign key constraint here. If I choose <code>metadata</code> as the parent table (which would be my preference, in the absence of a bidirectional constraint):</p> <pre><code>PRAGMA foreign_keys = ON; CREATE TABLE metadata (document INTEGER, page INTEGER); CREATE VIRTUAL TABLE data USING fts4(content TEXT, docid REFERENCES metadata); </code></pre> <p>I get <code>Error: vtable constructor failed: data</code> (unsurprisingly, because <code>docid</code> is an alias for <code>rowid</code>, but of course I can't use another column because all columns except <code>rowid</code> must be <code>TEXT</code>). </p> <p>Whereas if I try the other way round:</p> <pre><code>PRAGMA foreign_keys = ON; CREATE VIRTUAL TABLE data USING fts4(); CREATE TABLE metadata (document INTEGER, page INTEGER, docid REFERENCES data); </code></pre> <p>the table construction succeeds, but if I try:</p> <pre><code>INSERT INTO data (docid, content) VALUES (123, 'testing'); INSERT INTO metadata (docid, document, page) VALUES (123, 12, 23); </code></pre> <p>I get <code>Error: foreign key mismatch</code>.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload