SQLite Forum

About virutal table and FTS4
Login

About virutal table and FTS4

(1) By 6kEs4Majrd on 2021-04-03 05:08:11 [source]

I see VIRTUAL TABLE like the following. I don't quite understand how it works. My understanding is that it does not hold the real data. The data are from other tables. But for this specific case how to figure out where the data of each field is from?

sqlite3 -separator $'\t' getsploit.db 'select * from sqlite_master where type="table";'
table	exploits	exploits	0	CREATE VIRTUAL TABLE exploits USING FTS4(id text, title text, published DATE, description text, sourceData text, vhref text)
table	exploits_content	exploits_content	2	CREATE TABLE 'exploits_content'(docid INTEGER PRIMARY KEY, 'c0id', 'c1title', 'c2published', 'c3description', 'c4sourceData', 'c5vhref')
table	exploits_segments	exploits_segments	3	CREATE TABLE 'exploits_segments'(blockid INTEGER PRIMARY KEY, block BLOB)
table	exploits_segdir	exploits_segdir	4	CREATE TABLE 'exploits_segdir'(level INTEGER,idx INTEGER,start_block INTEGER,leaves_end_block INTEGER,end_block INTEGER,root BLOB,PRIMARY KEY(level, idx))
table	exploits_docsize	exploits_docsize	6	CREATE TABLE 'exploits_docsize'(docid INTEGER PRIMARY KEY, size BLOB)
table	exploits_stat	exploits_stat	7	CREATE TABLE 'exploits_stat'(id INTEGER PRIMARY KEY, value BLOB)

For FTS4, is it just to speed up the search?

So the virtual table could be created by CREATE VIRTUAL TABLE exploits (id text, title text, published DATE, description text, sourceData text, vhref text)? The only drawback is the search speed is much slower but the results should be the same whether FTS4 is used or not? Thanks.

(2) By Larry Brasfield (larrybr) on 2021-04-04 23:47:21 in reply to 1 [link] [source]

I'm answering because your question has languished, and not because I really understand its subtleties.

... I don't quite understand how it works. ...

The inner workings are not really described. The interface is described at the doc page I link below. If you want to understand how it works, rather than how to work it, there are plenty of articles on full text search technology to be found with a web search (which normally uses such technology.)

For FTS4, is it just to speed up the search?

That is the primary purpose of FTS(anything), as the FTS{3,4} docs indicate. I suggest you study them.

... but the results should be the same whether FTS4 is used or not?

The search using FTS relies on indexing of whole words, after they are subject to a "stemming" process. (You can find that in the linked doc or via a web search.) So for searches on words will turn up the same finds. Searches on parts of words are likely to turn up different results depending on whether FTS is used or a more brute-force searching/sifting method is used.