Full Text Index Search

[Erik Mueller-Harder]

Hi, James --

If I'm understanding your need correctly, Valentina already supports 
exactly what you're looking for.

I have an internal application which archives old e-mail messages, for 
example.  I've simply defined the message-body field as Indexed set the 
IndexByWords flag.

I simply split up the user's search field up into words and set up the 
SQL inquiry with an "OR" for each word.  RB fragment:

	sqlWhereClause = "WHERE messageBody IS '" + userWord(1) + "' OR 
messageBody IS '" + userWord(2) + "'"

etc.  Actually, what I do is a little fancier:  I build a dictionary 
out of the words in the user's search field (thus avoiding duplicates), 
and make sure that the *last* term that the user types matches against 
word-beginnings (using "Left" in the SQL and matching only the number 
of characters in the last term).

Depending on the size of your database, this can actually be done 
real-time, including the refreshing of the listbox.  Or, if that's too 
slow, it can be initiated from a timer firing every 0.5 seconds or so.  
Adjust to taste.

Good luck!

-- Erik Mueller-Harder


On Oct 25, 2004, at 15:53, James Milne wrote:

> 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.