问题描述:

I need to add FTS to an existing database.

Started to test external content FTS tables, where the FTS indexes reside in the default (main) DB. Everything worked satisfactorily, except a few things (such as index rebuild) could take considerable amount of time.

Then I read about the possibility to put FTS index into attached DB. This seemed to promise several advantages, hence I decided to give it a try. However, all my trials failed. Here are a few examples:

Situation

  • We have a table 'account' with a text column 'code', and

  • Want to create FTS index for that column and place it into separate database file

Test1) ERROR: near ".": syntax error

ATTACH 'ZipFts.sdf' AS ZipFts; CREATE VIRTUAL TABLE ZipFts.account USING fts4(content=account, code);

INSERT INTO ZipFts.account(ZipFts.account) VALUES('rebuild');

Test 2) ERROR: Stack overflow (infinite recursion inside sqlite engine)

ATTACH 'ZipFts.sdf' AS ZipFts; CREATE VIRTUAL TABLE ZipFts.account USING fts4(content=account, code);

INSERT INTO ZipFts.account(account) VALUES('rebuild');

Test3) ERROR: no such table: ZipFts.account

ATTACH 'ZipFts.sdf' AS ZipFts; CREATE VIRTUAL TABLE ZipFts.ZipFts_account USING fts4(content="account", code);

INSERT INTO ZipFts_account(ZipFts_account) VALUES('rebuild');

Test4) ERROR: no such table: ZipFts.main.account

ATTACH 'ZipFts.sdf' AS ZipFts; CREATE VIRTUAL TABLE ZipFts.ZipFts_account USING fts4(content="main.account", code);

INSERT INTO ZipFts_account(ZipFts_account) VALUES('rebuild');

Does anybody know how these things work? Thanks in advance.

网友答案:

After some searching in sqlite3.c I found what might be the answer.

Look at the bottom of the function fts3ReadExprList(). The name of the content table is prefixed with the DB name here! This explains everything.

Moreover, this seems to be the only non-trivial use of zContentTbl (= the name of the content table). When I slightly modified fts3ReadExprList() function as shown in the code underneath, the problem disappeared.

  // Code inserted by @JS-->
  // Do not prefix zContentTbl with the database name. The table might reside in main database, for example.
  if( p->zContentTbl){
    fts3Appendf(pRc, &zRet, " FROM '%q' AS x", p->zContentTbl);
  }
  else
  // <[email protected]
  fts3Appendf(pRc, &zRet, " FROM '%q'.'%q%s' AS x", 
  ...

Note that I did not test the code sufficiently. (So far I only know that the FTS index was created.)

Anyway, for the time being, I consider this an SQLite bug and I'll try to go on with my fix.

网友答案:

I think this is as designed.

If it were otherwise, the underlying table for an external content table could change as databases are attached or detached.

You might be able to achieve this using a contentless FTS Table though.

Dan Kennedy.

相关阅读:
Top