Subject | RE: [firebird-support] Re: Full Text Searching For Firebird |
---|---|
Author | Nigel Weeks |
Post date | 2005-11-28T21:47:17Z |
Fulltext search, with storage inside Firebird, is relatively easy.
I've used command-line PHP to do the job. I can post it here in small chunks
if anyone wants it.
Framework:
prepare.php: adds an rdb$rid field to each table that's seeded from a
system-wide generator
schema.sql: Adds two tables
- srch$word (contains unique words, soundex, and metaphone
representations)
- srch$locations (links the word to the rdb$rid in the table/field it
came from)
spider.php: looks for records without an rdb$rid value (i.e. where rdb$rid
is null), breaks each field into words, calculates soundex and metaphone,
and populates the two tables
fuzzysearch stored procedure: is then used with a join to search tables
select * from table t
join sp_fuzzysearch('string','soundex','metaphone') FS
ON T.rdb$rid = FS.rdb$rid AND FS.rdb$relation_name = 'table we are
searching in' and rdb$field_name = 'field we want to search in';
Whoosh - looks like a lot, but each component is relatively simple.
N
I've used command-line PHP to do the job. I can post it here in small chunks
if anyone wants it.
Framework:
prepare.php: adds an rdb$rid field to each table that's seeded from a
system-wide generator
schema.sql: Adds two tables
- srch$word (contains unique words, soundex, and metaphone
representations)
- srch$locations (links the word to the rdb$rid in the table/field it
came from)
spider.php: looks for records without an rdb$rid value (i.e. where rdb$rid
is null), breaks each field into words, calculates soundex and metaphone,
and populates the two tables
fuzzysearch stored procedure: is then used with a join to search tables
select * from table t
join sp_fuzzysearch('string','soundex','metaphone') FS
ON T.rdb$rid = FS.rdb$rid AND FS.rdb$relation_name = 'table we are
searching in' and rdb$field_name = 'field we want to search in';
Whoosh - looks like a lot, but each component is relatively simple.
N