Hello,
Fairly sure this hasn't been asked (recently), so please forgive me if it has.
I am current working on a site which uses SQLite to deliver page content (my hands are tied at this point in regard to db system). I have been asked to implement a search feature "similar to Google". I have read articles on SQlite's FTS capabilities but am still none the wiser - I have found very few examples, and am wondering if anyone could point me in the right direction? I understand I can use LIKE, but it is slow and not recommended.
I have read a few things about FTS1, 2 and 3 with SQLite and this seems the right direction, but does the stock build of SQLite 2.8.17 (PHP 5.2.8) contain them? This is partly rhetorical as I'll find this out quickly when trying it. However, the examples I have seen say that you should create a VIRTUAL TABLE using one of these extensions, populate the table with the data you wish to search, and then preform a MATCH query. Makes sense; I'm guessing the system indexes the data when it is INSERTed/UPDATEd. Now, my question is this: should I add all the searchable data to a FTS2/3 VIRTUAL TABLE when a search is demanded, or should I add/update to the VIRTUAL TABLE when an row is added to the main table (which contains the content to be made searchable)... ?... I am not sure if I understand the idea of VIRTUAL TABLES... If the VIRTUAL TABLE is written to the db file is the same fashion as anyother table, it would make more sense to populate the VIRTUAL TABLE when the main table is added to/updated...?
Has anyone had any experiences of using the sqlite_fulltextsearch class (http://phpclasses.fonant.com/browse/package/2328.html)? Any problems in using it? It seems too simple a system...
Any help or advice would be greatly appreciated,
All the best!