Full Text Index Search

[James Milne]

Hi guys & gals,

I use V4RB in a product I'm developing. I'm looking to implement a 
full-text search on a field in one of my tables.

This field contains a fairly short comment string. Usually it will have 
a maximum of around 256 to 512 characters in it. There will likely also 
be around a maximum of 50,000 rows in this particular table.

At the moment, I have an interface which allows the users to type in 
search terms, ala Google, into a text field. A listbox is populated 
with records in the table whose comment field matches the search terms 
entered by the user.

The terms are very simple. I don't require support for boolean 
operands. I simply look for any rows in the table which contain words 
starting with, or equal to, the search terms. All the search terms must 
be found in the comment for it to be a valid result.

As far as I can tell, Valentina doesn't support Full Text Indexing 
per-se. You can index on a text field, but the closest you can get to a 
full text index is to use the LIKE operator on the field.

I was wondering whether anyone knew of any ways to implement a full 
text index using Valentina? Alternatively, whether there was a good way 
to build ones' own full-text index? This is something I haven't spent a 
lot of time researching.

As far as rolling one's own solution goes: I'd been considering 
maintaining a table of words extracted from the comments. Each unique 
word, that passed a 'noise filter' test to remove small words like 
'it', 'be', etc., would be added to this table. I would then have 
another table which linked entries in the word table to entries in the 
table containing the comments. To perform the search, I would have to 
find the primary keys of each valid word in the search terms, then find 
all the record IDs which referred to those  words. My result set would 
be the intersection of the sets of comment record IDs for each word.

Whenever one changed the value of the comment, one would have to drop 
all the rows in the word->comment map table referring to the row 
containing the comment, then rebuild the word->comment mappings. This 
is fair enough, as the comment fields do not change frequently in this 
application.

I was just wondering whether anyone else has had to solve a similar 
problem using Valentina, and whether this is an efficient approach.

--
Kind Regards,
James Milne