nn33dl3 wrote:

> Does anybody knows how to do a full text search on a firebird database
> are there some open source scripts?

You could probably do it with triggers, but it takes some work. Example:

create table a
(
id integer not null,
textfield varchar(50)
);

and you want to do a fulltext search on "textfield".

Now, create a table:

create table a_fts
(
id integer not null references a(id) on delete cascade,
word varchar(50)
);

And create a trigger that will insert/update/delete from that table when table "a" is changing. For
example:

insert into a values (1, 'This is complex');

Should also do:

insert into a_fts values (1, 'this');
insert into a_fts values (1, 'is');
insert into a_fts values (1, 'complex');

You may need to use some UDF or stored procedure for that trigger, I leave it for your homework ;)

Anyway, that's all the hard work. Later you just query like:

select id from a_fts where word = 'complex';

or

/* OR type of query */
select id, count(*)
from a_fts
where word in ('complex', 'query')
group by id
order by 2 desc

or

/* AND type of query */
select id
from a_fts a1
join a_fts a2 on a1.id = a2.id and a2.word = 'query'
where a1.word = 'complex';

You also need to put indices on tables to make it work fast.

--
Milan Babuskov
http://abrick.sourceforge.net